MySQL

MySQL Multiple Table Search

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2005

Posts: 1

Reputation: jaguarcy is an unknown quantity at this point

Solved Threads: 0

jaguarcy

Offline

Newbie Poster

MySQL Multiple Table Search

Aug 19th, 2005

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.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
(SELECT * FROM a WHERE id=1) UNION
(SELECT * FROM b WHERE id=1) UNION
(SELECT * FROM c WHERE id=1) UNION  ... etc
(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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * FROM a,b,c WHERE id=1
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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * FROM a,b,c WHERE a.id=1 OR b.id=1 OR c.id=1
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?