Select from multiple tables

[ikrajinovic]

Hi guys,

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:

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
");

I do this becouse first I want to show the results with one word, then two, then three, and others.

Is there any other way to do this becouse this way is very slow.

[Luuk]

I tihnk this:

var1 LIKE '".$searchWord." %'

is a subset of this:

var1 LIKE '% ".$searchWord." %'

I do this becouse first I want to show the results with one word, then two, then three, and others.

This is not guaranteed to work this way.. ;)

If you really want the data from Table1 first you should do something like:

SELECT * FROM (
SELECT 1, t1.* FROM table1 t1 WHERE t1.var1 LIKE '".$searchWord."' 
UNION
SELECT 2, t2.* FROM table2 t2 WHERE t2.var1 LIKE '".$searchWord."' 
.....
)  ORDER BY 1, var1

The ORDER BY in the SELECTs is also slowing down your query, the only place you need it (here) is at the end of your query..

[ikrajinovic]

I don't want to use

var1 LIKE '% ".$searchWord." %'

becouse I want to show only records that contains my search word at beginning of the record.

[ikrajinovic]

What is a difference between your code and mine?

[Rabbit]

You have no other way of doing it. You should think about normalizing your data. Why are there different tables? It should just be one table with all the data. You should also put on indexes if you haven't already done so.

[ikrajinovic]

I've been thinking about that. I'm not sure how to do that. Can you please help me?

[Luuk]

I've been thinking about that. I'm not sure how to do that

Rabbit suggested two things, which of the two where you thinking about, or do not know how to do?

Also a question was asked ("Why are there different tables?"). I think this is a hind to the first item Rabbit suggested...

In my earlier reply i said that:
var1 LIKE '".$searchWord. " %'
is a subset of:
var1 LIKE '% ".$searchWo rd." %'

Therefor it makes not much sence to do:
var1 LIKE '".$searchWord. " %' OR var1 LIKE '% ".$searchWo rd." %' OR var1 LIKE '% ".$searchWord." '

Because this would give the same result as:
var1 LIKE '% ".$searchWo rd." %'
which you said you did not want to do.....

[ikrajinovic]

But I need results where my word is on beginnig of the string or in the middle with space before it.

In example if I search for test, I want next records:
test
test drive
dope test


I don't want to display: testing

[Rabbit]

Did you normalize your data? Did you put indexes on the fields?

An additional optimization you can do is to convert the OR conditions into a UNION ALL query.