I have four table with the same structure (id, var1, var2)
Table 1 in var1 have only one word (150000+ records)
Table 2 in var1 have two words (50000+ records)
Table 3 in var1 have three words (50000+ records)
Table 4 in var1 have four or more words (30000+ records)
What is the best way to search them?
I do search with UNION as following:
Expand|Select|Wrap|Line Numbers
- SELECT * FROM (
- (SELECT * FROM table1 WHERE var1 LIKE '".$searchWord."' ORDER BY var1)
- UNION
- (SELECT * FROM table2 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
- UNION
- (SELECT * FROM table3 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
- UNION
- (SELECT * FROM table4 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
- ) AS WHOLEDATABASE ORDER BY var1
- ");
Is there any other way to do this becouse this way is very slow.