By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Select from multiple tables

P: 5
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
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,031
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

P: 5
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

P: 5
What is a difference between your code and mine?
Apr 30 '12 #4

Rabbit
Expert Mod 10K+
P: 12,347
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

P: 5
I've been thinking about that. I'm not sure how to do that. Can you please help me?
May 3 '12 #6

Expert 100+
P: 1,031
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

P: 5
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
Expert Mod 10K+
P: 12,347
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.