Say there are 1,000 tables in a database, and each table has 1,000 rows. When I search for single table from these 1,000 tables, is the search time same as that required to search for data within one of the tables?
In other words, does SQL use the same search algorithm to find a table out of 1,000 tables as it does to get data from a table with 1,000 rows?
Advertisement
Answer
No, MySQL doesn’t use the same search algorithm to find a table.
MySQL maintains an in-memory “data dictionary” so when you run a query that names a specific table, it looks up that table very quickly. It’s much faster for MySQL to identify a table than to search for data within a table. For example, the database servers I maintain at my job have over 150,000 tables, and this isn’t a problem.
Does this mean you should split up your data over many tables to make it run faster? No — that’s not usually a good tradeoff. It makes your application code more complex, since your code needs to pick which table to query. You may also find cases where you wish the data were in one table, if you have to search for results across many of your tables.
Here are a couple of principles to follow:
“Everything should be made as simple as possible, but not simpler.” (attributed to Albert Einstein)
“First make it work, then make it right, and, finally, make it fast.” (Stephen C. Johnson and Brian W. Kernighan, 1983)