I’m designing a mysql based db which will hold a couple of million records in each of its main tables (profiles, profile properties, related messages, etc.) and some of these tables should be joined to provide resultsets to the connected php app. I suspect these JOINs would result a serious drop in performance.
It’s possible to slice the tables by region codes, so I could make let’s say 50 tables from each type, instead of one. As there are some cross region data in the database it would result some storage overhead (cross region data should be stored as duplicates).
As the user chooses the region(s) he/she is interested in, it would be possible that some queries would involve all 50 regions and the individual result sets would be merged using UNIONs.
Is it a preferred approach or would you prefer some other methods?