468,541 Members | 1,808 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,541 developers. It's quick & easy.

Select from multiple tables

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM (
  2.     (SELECT * FROM table1 WHERE var1 LIKE '".$searchWord."' ORDER BY var1)
  3.     UNION
  4.     (SELECT * FROM table2 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  5.     UNION
  6.     (SELECT * FROM table3 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  7.     UNION
  8.     (SELECT * FROM table4 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  9.     ) AS WHOLEDATABASE ORDER BY var1
  10. ");
  11.  
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.
Apr 29 '12 #1
8 2059
Luuk
1,043 Expert 1GB
I tihnk this:
Expand|Select|Wrap|Line Numbers
  1. var1 LIKE '".$searchWord." %'
is a subset of this:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM (
  2. SELECT 1, t1.* FROM table1 t1 WHERE t1.var1 LIKE '".$searchWord."' 
  3. UNION
  4. SELECT 2, t2.* FROM table2 t2 WHERE t2.var1 LIKE '".$searchWord."' 
  5. .....
  6. )  ORDER BY 1, var1
  7.  
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..
Apr 29 '12 #2
I don't want to use
Expand|Select|Wrap|Line Numbers
  1. var1 LIKE '% ".$searchWord." %'
becouse I want to show only records that contains my search word at beginning of the record.
Apr 30 '12 #3
What is a difference between your code and mine?
Apr 30 '12 #4
Rabbit
12,513 Expert Mod 8TB
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.
Apr 30 '12 #5
I've been thinking about that. I'm not sure how to do that. Can you please help me?
May 3 '12 #6
Luuk
1,043 Expert 1GB
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 '% ".$searchWord." %'

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

Because this would give the same result as:
var1 LIKE '% ".$searchWord." %'
which you said you did not want to do.....
May 3 '12 #7
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
May 8 '12 #8
Rabbit
12,513 Expert Mod 8TB
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.
May 8 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Ahmet Karaca | last post: by
8 posts views Thread by Jason L James | last post: by
5 posts views Thread by Craig G | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.