I have a MySQL database design which consists of several tables that are identical in structure, but contain different sets of data (each table is assigned to a specific group of people). This is because each table might grow to several tens of thousands entries, and with a prospect of up to a hundred (or more) groups, I wanted to keep things small for each group.
There is a SINGLE case where I'd need to perform a search on an index field accross ALL tables. JOINS haven't worked, and the only thing I've managed to get to work was a UNION. But a UNION accross a hundred tables, i.e.
(SELECT * FROM a WHERE id=1) UNION
(SELECT * FROM b WHERE id=1) UNION
(SELECT * FROM c WHERE id=1) UNION ... etc
will get VERY expensive, VERY soon. The syntax
SELECT * FROM a,b,c WHERE id=1
obviously doesn't work because the field id is not unique, and if I change the syntax to
SELECT * FROM a,b,c WHERE a.id=1 OR b.id=1 OR c.id=1
then the result I get is not what I was looking for (the results are joined).
Does anybody know a better way of searching accross multiple identical tables on a single field?
