efficiency for data

[msaskin]

Ok, I'm not a MySQL guru, so i've got a question for all of you out there.

Is it more efficient to have 500 tables each with 18,000 records in them or to have one giant table with 9000000 (500 * 18000) records. Or is some other permutation the most efficient.

Thanks for your help.

~Matt

[[deleted]]

500 tables is unmanagable.

If you are looking for one particular record, you may have to run 500 queries...
A Join would take ages...

The only drawback of one big table vs many small ones is that MySQL locks the entire table during an insert, forcing all other queries to wait.

[badman]

hello
totally agree with Vincent.

To speed up your searches you can think of indexing the tables on columns which appear frequently after WHERE keyword.

This will slow insert operations a bit but select will be a lot faster.

To see the difference try using EXPLAIN keyword before a select query.

explain select * from xyz where ......;

In the output under the column named rows a number is present, this is the number of records 'mysql' thinks it has to look for to locate the record desired by you. This should give you an insight i hope.

cheers!

[msaskin]

thanks for your responses. To get over the table locking problem, these tables are never going to have inserts into them once the original data is in them. They are for querying only.

[[deleted]]

In that case, you should definately not split them up.

Remember, this is MySQL we are talking about. It uses cooked-files to store it's data, and it doesn't support database-fragmentation.

No matter how many tables you create, there's still only one database server, and the data is all on one harddisk,
so you just add overhead from having to find out which table the record is in.

You could solve that by using a JOIN, but that just takes all your tables and makes one big table again :-)

Splitting up a table is only an option when:
- You have serious locking issues (which you say you don't)
- You can store the two parts of the table on different harddisks, increasing access-speed (which you can't with mysql)

The disadvantages of splitting a table up:
- You can't use auto_increment columns for inserts, because two columns will get two seperate auto_increment columns, so doublicate values will exist between the columns (can't merge the data anymore)
- You need a way of findingout which table a record is in without having to query each table.
- More tables means more table-data for the engine to keep track of. This might even take more memory than for one big table.
- Doing a select on all the data at once (for reporting) will require complex JOIN statements, which take forever in MySQL.