0

I'm making a database which should be able to handle over 1,000,000,000 rows.

in order to optimize it's performance i've decided to split tables.

so instead of having 1,000,000,000 entries in one table for over 10 years i would split it to 3650 tables and one table for each day.

there would be no more than join or union of 2 queries now i want to know wether having too many tables degrade performance or not and if so How much ?

|improve this question
2

Overall, splitting tables adds overhead for a perceived benefit that isn't there. Tables of a few billion rows do need to be carefully designed as far as index and normalization, however splitting adds computation /software overheads and may generate far worse queries.

To directly answer the question:

Too many tables on disk will have no impact on MySQL (unless you have a filesystem that can't rapidly open a filename from a very full directory (mitigated in 8.0 (frm data in tablespace) and innodb_file_per_table=0).

As far as active tables, those being used frequently in queries there are effects from:

table_open_cache limits the number of tables cached and so does innodb_open_files.

Exceeding these cache limits will cause tables to close and open and need to be re-examined adding overhead to queries.

|improve this answer
  • i'm seeing the difference in action. i've made 3650 tables mostly empty but ten of them with hundreds of thousands of entries and another table in same database with 2 million entries . the table with 2 million entries is noticeably slower than those with 300,000 entries. by the way as i am not going to index all 3650 tables because of ram issues but even without indexing they work fine. as i said my code is day based so when user wants to check certain day i know which table to open and leave other tables. – Pooya Estakhri May 19 at 23:59
  • wont mysql try to load all indexes of all tables into the ram ? – Pooya Estakhri May 20 at 1:30
  • 2
    Nope, it loads and caches index pages in innodb exactly like innodb data pages (in the same buffer pool) which are treated like a LRU cache. Its probably time to look closely how MySQL works internally than continue on with incorrect assumptions. – danblack May 20 at 1:49
  • official innodb buffer pool manual, first line "The buffer pool is an area in main memory where caches table and index data as it is accessed". The aaspisfun.com reference is talking about MyISAM. The point of asking question is to get advice. Surely its when its received it should be worth considering, even if its not exactly what you want. – danblack May 20 at 2:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.