MYSQL :: Large Number Of Tables

Large Number Of Queries
Ive currently got a loop on a php page which takes ~7000 rows from an sql table, parses information from it and does inserts/updates/deletes depending on the information.
At the moment, it does a new mysql_query for each of the inserts/updates/deletes so it ends up doing ~10k queries each time its run.
Im wondering if its better to use an array to store the queries and then put them all together (";" seperated) and run all the queries at once in the same mysql_query.
One thing to bear in mind is some of the data involved in the queries relies on previous data from queries in the loop, would this cause any problems with the combined query

View Replies !

Large Number JOINs And With Two FK To One Table
1) I have a first table with a PK (OwnerID) and a large number of FKs

2) I want to return the 'filename' column from the tables referenced by the FKs (and wonder if there is a better/faster/more concise way to do it than the JOIN syntax below?

3) In one instance there are 'bookcaseRight' and 'bookcaseLeft' fields in the first table which both reference the same 'bookcases.bookcaseID' -- and I cannot figure the way to do this (and/or/union all give improper results)

Simplified code below and Thanks in advance:
---------------------------------------------------

SELECT
doors.filename AS door,
bookcases.filename AS bookcaseL,
bookcases.filename AS bookCaseR,

FROM offices
JOIN doors ON offices.doorID = doors.doorID
JOIN bookcases ON offices.bookcaseR = bookcases.bookcaseID

{?AND?OR?Union?}
offices.bookcaseL = bookcases.bookcaseID

WHERE offices.ownerID = 1

;

View Replies !

Number Of User Accounts For Large Database
I have a large database (816 tables with 11 fields each, each field could hold up to 50 entires) and I'd like to know if each table should have its own user account (created in Cpanel and used in the config file to access the database).

May sound like a simple question to some but I need to know in case we have a lot of traffic and 1 user account is not "enough" for all visitors to access the database info.

View Replies !

UPDATE Large Number Of Rows With CASE Is Too Slow
UPDATE table SET rating =
(CASE uniq
WHEN 'ACD' THEN 0.6857
WHEN 'GGYB' THEN 0.5831
WHEN 'QUCG' THEN 0.5831
WHEN 'CYP' THEN 0.5831
...
ELSE 0 ....

View Replies !

UPDATE Large Number Of Rows With CASE Is Very Slow
UPDATE table SET rating =
(CASE uniq
WHEN 'ACD' THEN 0.6857
WHEN 'GGYB' THEN 0.5831
WHEN 'QUCG' THEN 0.5831
WHEN 'CYP' THEN 0.5831
...
ELSE 0
END), score = (SELECT @t := 1/(1+rating))*votes_up - (1-@t)*votes_down;

The table has the column `uniq` as PRIMARY KEY of type VARCHAR(10), and about 500 rows. I need to update the entire table with this query. The problem is the query runs extremely slowly, taking 4.6529 seconds to run. I realize the index is not the most efficient, but is it the main culprit?

View Replies !

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

View Replies !

Large Tables
So i've got a table with 15 million rows. id is the primary key

SELECT COUNT(id) FROM listings WHERE id != 0;

Takes about a minute to load... which is insanely slow..
anyone know what could be causing this?

View Replies !

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

View Replies !

Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life:

If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient?

The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key.

The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?

View Replies !

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

View Replies !

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

View Replies !

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

View Replies !

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.
This is very new to me and I am noticing that my queries are really slowwwwww!
What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

View Replies !

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

View Replies !

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

View Replies !

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

View Replies !

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

View Replies !

Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size.

The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table.

But the question I have now, can unused large tables affect the perfomance of Mysql?
I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )

View Replies !

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

View Replies !

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

View Replies !

Random Records :: Rand() On Large Tables?
Im told that ORDER by rand() isnt a good idea to use on large tables, so Im interested to know how else this could be done. I need to display 20 random records (there are conditions in the WHERE clause as well).

View Replies !

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

View Replies !

Create Table Syntax For Large Tables
How do i create tables with sizes larger than 4 GB? Do I need to specify any additional clauses such as heap etc when creating tables?

View Replies !

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

View Replies !

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

View Replies !

Number Of Tables In Db
will having like 100 tables in 1 database on a pretty heavy traffic site slow performance? or splitting into 2 db's make a difference?

View Replies !

Number Of Tables
just a quick question, some database designs i've seen seem to want to compress everything into as little tables as possible, whereas others can go into a hundred rows for a system that doesn't seem to be that much more complex.

A wordpress database is fairly tiny at only a handful of tables, whereas zen cart has about 100+ from memory, and yet I don't see either one as significantly more complicated than the other (although I don't know much about Zen Cart).

I do understand the concept of keeping data in logical divisions, but I wonder how far one should push that towards keeping the number of tables to a minimum - no doubt purely just from a human management standpoint....

View Replies !

Managing A Very Big Number Of Tables
I have to manage 2^19 (approximately 524 000) tables, should I divide them into several databases to make it easy for my operating system (ext3 file system) and for my MYSQL server ?
And what storage engine is the best in my case ?

The second question : with the "min_rows" option can I avoid fragmented tables on my hard disk in my case ?

View Replies !

Optimal Number Of Tables
I know there are various ways to configure a database setup and therefore affect performance tweaks, but in general is there a limit to the number of tables you can have in a database after which performance would be impacted?

View Replies !

Maximum Number Of Tables?
What is the maximum number of tables you can put into a mysql database?

View Replies !

Max Number Of Tables In A Database?
is there a "maximum limit" for tables in a database -- before the database gets clogged up and slow with every fetch? (as in the case for too many rows in a table.)

View Replies !

Share ID Number For Multiple Tables
In a MySQL database I have: 'table_a', which some columns and an auto_increment id. I also have 'table_b', which has some columns but no id. I need to fetch rows from both tables, that are from the same id/entry. I could simply select the row number but if a row was dropped from 'table_a', it would become out of sync with 'table_b'.

Is there a way to somehow share this id from 'table_a' with 'table_b'?

View Replies !

Join Tables To Count Number Of Records?
I've two tables:

responses
==========
response_id
schema_id
timestamp

answers
==========
answer_id
response_id
answer

(that's a cut down version, but will do for this question)

I need to get all the responses where there is at least one answer in the answers table. But I do not want the answer data. It's literally a quick check for an export to say "get me all the responses where there's at least one question answered". I have this: Code:

View Replies !

Is There A Relation Between Number/size Of Total Tables & Table Operations In The Same Db
Does the number of tables and their total size in one DB influence the time needed by MySQL to perform a SELECT/UPDATE/DELETE operation on a certain table?

I'm wondering about how I should backup information, which become obsolete on a monthly basis and therefore should cease to exist in the table. Just keeping the records in the table and append the new information would certainly slow down all of my queries. Now the question is: is there any disadvantage of creating new tables for each set of data(monthly) in the same database compared to simply creating an external file backup?

View Replies !

How To Add Count Number Of Words With Showing Limited Number
SELECT SUBSTRING_INDEX(classDescription,' ',13) as classDescription, listing.inv_company, listing.inv_addr2, listing.inv_state, listing.classcatid, listing.industryid, listing.listingID, listing.inv_country, listing.adscreation_date

View Replies !

Formatted Number Column Cannot Be Sorted By Number
Columns come in and are built based on XML doc and the HTML (smarty mysqllate) does not know weather this is a number to format it nicely such as 2345.993 to 2,346.99.

So I do my formatting in the XML which gives the dynamic query developer something similar this:

FORMAT(price * qty,2) AS sales

All was fine and dandy until i wanted to sort it by number. Turns out Format makes it as string and it does a string sort, in other words it would sort it like this:

1223

21

90

So a genius (/sarcasm) told me to "add a zero" to it and it would work.

it turned 2,345.00 into 2.00. (had to explain to the client why his biggest sale was showing up as 2.00)

Now i've converted it to ROUND()

but is there any other way to have the comma (thousands delimiter) as well has have mySQL treat it as a number?

View Replies !

Large Db
i have some problem when trying make insert a lot of row in a large db.
I have to pharse a 10 file of 1 million of record and insert it into a
table ...
when arrive at around 1 million of record, every 1000 / 2000 records,
the query take 1 or 2 second to make 1 insert, then restart with a
normal fast inserting ...

in first i think was an hardware problem (like hd with bad sector or
similar) but i changed pc and the problem persist

the so is slackware 10.1 with recompiled mysql 5.1
the client program that made the insert is a vb application and the
odbc is 3.51

View Replies !

Large SQL
Just wondering if there is a better way to view SQL code. Reason I ask this is because I have downloaded a backup of my database onto my local machine (desktop) and the only way I can view the .sql file is in Note Pad but it takes ages to load.
Is there any software that allows me to view my msSQL backup?

View Replies !

Large Databases
We are in the process of building an application to front end a
database of about 2 million records. I would like to know if MYSQL can
handle this and if not, what would be a good database to go with. We
would like to use opensource as much as possible, but if MYSQL cannot
handle this kind of volume then we will have to go in for commercial
software.

View Replies !

Precaution For A Large Db
I have to handle a very big (~50 gigabyte) database (quite all in a
table), and I am going to use mysql.
Can mysql handle all that ammount of data?
What problems I am going to have?
Should I take any precaution?
Is some other database-engine more suitable for this work?
Posgresql maybe?

View Replies !

A Large *.sql Import
I am trying to import a very large .sql file of about 165mb. When i try to import the file i use "source C:DB.sql" and it runs for a bit and then stops. When i try to query on this table it crashes. But when i do "show columns from DB;" is show me the information correctly. I did update my my.ini config file with:
innodb_data_file_path = cseibdata1:10M:autoextend
still not working.

View Replies !

Importing A Very Large DB
I have a rather large .sql dump that I was given to work with thats about 3 gig. I cannot get a smaller .sql dump unfortunately.
I am using wamp server on my local machine and importing this database will take forever. I have two questions......

Can I optimize the amount of memory or processor dedicated to php or mysql to speed this up? Is there a way to add some sql parameters to the import to only import parts of the database I am working with? One of the columns is a unix timestamp which would also solve my problem.

View Replies !

Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.

View Replies !

Very Large Bin Logs
We're getting several very large binary logs, reaching overall over 10GB which is a problem:

- When does mysql decide to purge the logs if at all
- Is there a way to control the number of bin logs created
- If the bin log size is set to a small number with max_binlog_size, will we get lots of small files ?

View Replies !

Large Table
I have a website with over 36,000 registered users. I have a table with 5 million+ records, each of which correspond to a particular user. Obviously some users are tied to a few of these records, others are tied to thousands.I am trying to decide:

-Keep everything in the one table, and allow it to grow infinitely large.
-Create an individual table for each of the 36,000 users and keep only their records in their respective table.

View Replies !

My Table's Name Seems Too Large :S
If I execute a query to a mysql datasource and select any table that has this name:

select * from 2006-catalogo-productos

.... gives me an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#55614;&#57158;-catalogo-productos' at line 1

but, i don't have that effect if I select another table of the same datasource, eg:

select * from artistas

The only difference seems to be that one table's name is larger than the another. I can't change the table's name... Â¿any idea?

View Replies !

VERY Large Database
I have such question - how I should split large data for the better performance?
Its seems to be old question, but can't find it in search

Lets say if I have 10000 items, each have 10000 records - at the start and much more later.
Main operations - INSERT/SELECT (search for the records on some item by key)
DELETE/UPDATE almost will not be used (because of the implementation)

I see few possible options
1. Create 100 databases, in each 100 tables, in each 10000 records (records for the 1 item).
2. Create 1 large table with internal index.

As I understand, main difference between 1 and 2, that in case 2 main load will be drived to the mysql and index, in case 1 main load will be drived to the FAT (or whatever else used at the server).

But before mysql will use index large file (or its index) will need to be loaded, so in case 2 FAT will be loaded too.

IMHO option 1 is better from performance side.

View Replies !