UNION of Smaller Tables vs. SELECT from Larger Tables

MySQL & MariaDB Other MySQL® Questions
1

I tried searching google and these forums, but couldn’t find any answers for a similar situation.

I am building a new database and trying to determine if there are any real/noticeable speed/performance advantages among the following setups. Assuming roughly 50 records per subcategory per group (20 subcategories per category), 1,000 records per category per group (10 categories), 1,000 groups per database.

Estimating approximately 1,000 records per query (50 records per subcategory times 20 groups).

Setup 1: 1 overall table with 10,000,000 records - 1 table queried

Setup 2: 10 category tables with 1,000,000 records - 1 table queried

Setup 3: 1,000 group tables with 10,000 records - 20 tables queried

Setup 4: 10,000 group/category tables with 1,000 records - 20 tables queried

My brain’s a little fried so math might be a little off - but gives the general idea.

Thank you for your help in figuring this out.

2

If you hesitate - benchmark.

For simple selection I guess main table will be rather optimal solution it also makes it easier if you want to query mutliple categories at once. You also have pretty small size of the data.

Many tables help when you’re fighting locks or data is large and you want to keep its size managable.

UNION requires temporary table an so relatively expensive.