Mysql queries

[Adolf_1]

I am building a system that will take about 20 million records. I am confused, if I should put all these records in one table or I should split it among severals tables to speed up the performance.

[diafol]

20m records doesn't seem like a huge number for a MySQL table. ISAM and InnoDB have (I think) 2^32 record limit (2^64 depending on set up) and 64TB limit respectively. If all records could potentially be queried, then maybe have them in one table. If records could be archived after a certain date for example, then that should speed up queries - no point searching records you know won't be included in query results. Depending on how your data is structured, you may benefit from partitioning your table, which effectively divies up your table into smaller tables, although that's not a good description. I read somewhere that user-defined partitioning was undergoing a makeover in MySQL 8.0 - so be aware and read all the docs if you go down this route.

[Adolf_1]

Thank you very much, but I am thinking of efficiency. What will be the performance of the system when retrieving about 20 million records in one table? Will it be fast or slow with time?

[diafol]

It will obviously be slower the more records the engine has to search - probably non-linearly. It depends on your indexes and the nature of your datatypes and content. You need a firm handle on these and research the relevant engines available to you.

Will it be fast or slow with time?

What does that even mean?

[Adolf_1]

I have understood you perfectly. Since the more the records, the longer it takes to search, I was thinking that it would be efficient to split the records among several tables or you think it will not make any difference.

[diafol]

I think I've answered that in my first post.

[Adolf_1]

Thank you very much.