MySQL Multi Table Search

Question

table1
 ID
 SUBJECT
 CONTENT

table2
 ID
 SUBJECT
 CONTENT

table3
 ID
 SUBJECT
 CONTENT

... 5 more

I want to search SUBJECT on all the tables

How can I do this?

Are the tables all the same structure? If so, why aren't they normalized into one table? If not, how do you plan on combining the results? Are you doing a FULL-TEXT search or just a regular search? — mellamokb, Oct 06 '11 at 18:25
All the same structure as Tables But Contents of the different. — Awersione, Oct 06 '11 at 18:29

score 2 · Answer 1 · answered Oct 06 '11 at 18:26

2

CREATE VIEW multitable AS
SELECT * FROM table1
UNION SELECT * from table2
UNION SELECT * from table3;

SELECT subject FROM multitable ...

answered Oct 06 '11 at 18:26

unutbu

842,883
184
1,785
1,677

its working but my db total size 1.5 GB and this method wait to much. do you have any idea ? – Awersione Oct 06 '11 at 19:24
How does the performance compare to the other solutions suggested here? What does `EXPLAIN SELECT subject FROM multitable...` say? What does running `EXPLAIN` on the other solutions say? Note that you can also profile the queries with `SET profiling = 1`, `SELECT ...`, `SHOW PROFILES`. – unutbu Oct 06 '11 at 19:42

score 0 · Answer 2 · answered Oct 06 '11 at 18:24

select * from table1 where subject like '%match%' union
select * from table2 where subject like '%match%' union
select * from table3 where subject like '%match%' union
select * from table4 where subject like '%match%' union
select * from table5 where subject like '%match%' union
select * from table6 where subject like '%match%' union
select * from table7 where subject like '%match%' union
select * from table8 where subject like '%match%'

score 0 · Answer 3 · answered Oct 06 '11 at 18:27

Because all the tables have the same syntax, you can use the UNION operator.

SELECT * FROM Table1
UNION Table2
UNION Table3
UNION Table4
UNION Table5
UNION Table6
UNION Table7
UNION Table8
WHERE SUBJECT="Subject"

For the sake of simplicity, 8 tables isn't too much to write out. If you had more, I'd recommend a dynamic query.

MySQL Multi Table Search

3 Answers3