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 ?
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.