I work on a system where we have a table with millions of rows where the primary key is a customer_id. We have about 8000 customers. I am about to lift the software onto a new major release and was wondering if it would make sense to create 8000 tables, one per customer and thus can cut the index short of one value - the customer id.
So far i have checked on limiting factors of mariadb or unix regarding single files in a folder and such, and nothing would prevent me on doing something like that. Rev3 of "High Performance Mysql" O`Reilly does not say anything either about it. But we all know, beeing able to create 8000 tables does not mean its a good idea when going into production.
Did someone ever did something like that and has real life experience ?!
thx in advance
No, no, eight thousand times no! Don't do this.
Yes, it's possible to create that many tables. Yes, you can probably get it to work. But you'll slow things down and hammer on internal stuff in the DBMS like the cache of open files.
Indexes, even multicolumn indexes, are far cheaper to manage than lots of different tables.
Before you refactor something like this, please consider optimizing your queries.
The same considerations apply to creating thousands of partitions. Partitions are mostly a holdover feature from the days of small and slow hard disk drives.