I have to deal with pretty big data, such a table (say BIG_TABLE) with 16.000.000 of rows. The table has just one primary index (a simple int).
I thought that splitting data among multiple tables would have boosted performance, so I put my data in 16 different tables (everything is on one disk). The first table had IDs from 1 to 1.500.000, the second the range 1500001-3000000 and so on. When I made performance tests, I found that I didn't get any performance gain. So I tried (just for test purpose) to create a table of 100000 rows (say SMALL_TABLE) with some data (IDs from 1.000.000 to 1.100.000) from my big table BIG_TABLE. Tests were like follows: (pseudocode) for 100000 times read a column from BIG_TABLE where ID=<random number between 1.000.000 and 1.100.000> for 100000 times read a column from SMALL_TABLE where ID=<random number between 1.000.000 and 1.100.000> The results are the same! There is no noticeable difference between a table with 16M rows and a table with 100K rows! Is that because my IDs are simple int from 1 to n (without gaps)? I've tried to do 100000 inserts in both tables, and BIG_TABLE takes 45 seconds while SMALL_TABLE takes 43 secs (not such a big difference for a table 160 times smaller!) The only big performance gain I got is from a SUM on a big range, because MySql in this case does a full scan on the SMALL_TABLE which is faster than an access by index on the BIG_TABLE. Am I doing something wrong? Isn't splitting supposed to improve performance? Or it is true only if you can split data on multiple disks? Thank you Leonardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]