I have a database that has 60 million+ records. The current set up is there is 1 table with 30+ million and a couple small tables with 5 million (ish) in each one. The data structure is the same for each table. The person who had created our search the first time (3-4 years ago before i was here) used multiple small tables. We are using match against
for each with joins. My boss and him were under the impression that using multiple tables lets MySQL search each table simultaneously. In everything I read everyone says that one big table would be better but as the 30+ million table get bigger its seems to be slowing down significantly taking 40+ secs sometimes. Is this slower than it should be?
The select statment
SELECT $stuff FROM table1 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table2 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table3 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table4 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table5 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table6 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table7 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table8 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table9 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE) UNION ALL
SELECT $stuff FROM table10 WHERE MATCH (Name) AGAINST ('+john +smith' IN BOOLEAN MODE)
The tables are MyISAM and there is a full text index on col Name
. Table3
is the one that has 30+ million records (approx 10gb). Would putting it in one table or splitting it up make much of a performance increase? I am missing something else? Or is 60+ million records to big to get a quick response on fulltext search?