One big table or two identical ones?
When two sets of data have the same structure, why one indexed table usually beats splitting them.
From the archive: first published on mohamedsharaf.net in February 2013. Rewritten and checked in 2026; some details may be out of date.
A question that came up on a project: two kinds of records with exactly the same columns. Two tables, or one table with a type column? I usually choose one table:
- Speed: with the right index, finding a row takes about the same time whether the table holds a thousand rows or a hundred million. B-tree indexes grow very slowly with size.
- Simpler design: one set of foreign keys, one set of queries, one place to add a column.
- Joins: joining a table to itself can even be slightly faster than joining two copies, because its data and indexes are already in memory.
Index the columns you filter on (for example (type, created_at)). And if the table really grows huge, MySQL partitioning can split it physically while your code keeps seeing one table.