MySQL Multiple Table Search

[jaguarcy]

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?