Welcome to WebmasterWorld Guest from 23.22.182.29

Forum Moderators: open

Message Too Old, No Replies

select from multiple tables

select from multiple tables

     
1:13 pm on Sep 10, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 15, 2010
posts: 126
votes: 0


I have 5 tables having the same fields if i have to search any keyword say foo i am using union in my select queries to find foo from all of the 5 tables but due to thousands of records in each table union is making it too slow to say. Is there any other way i can use in my query?
7:12 pm on Sept 10, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Why not join?

select * from table
left join table2 on table2.rid=table.id
left join table3 on table3.rid=table.id
left join table4 on table4.rid=table.id
left join table5 on table5.rid=table.id
where table.fld='foo' or
table2.fld='foo' or
table3.fld='foo' or
table4.fld='foo' or
table5.fld='foo';
8:39 pm on Sept 10, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


But if it's not in table (the first table), that query won't return any results.

Having thousands of rows shouldn't be too bad. We regularly query tables with millions of records and get results very quickly.

Make sure each table is indexed properly.

Also, do you absolutely need to have 5 tables?
10:08 pm on Sept 10, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


I have always been under the impression that using UNION is faster and can be considered optimized if you use it over a bunch of OR statements.

This:
SELECT * FROM tableA, tableB WHERE tableA.field1 = tableB.field2
UNION
SELECT * FROM tableA, tableB WHERE tableA.field3 = tableB.field4

Is faster than this:
SELECT * FROM tableA, tableB WHERE tableA.field1 = tableB.field2 or tableA.field3 = tableB.field4

As LIA said, make sure you have properly indexed your fields. Also, if you don't need to return all columns then change * to just the fields you need, that will also help speed things up.
1:34 am on Sept 11, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 15, 2010
posts: 126
votes: 0


Thanks for all replies. I must be doing wrong in indexing fields. Please correct me:

(select * from table1 were myfld like '%keyword%') union (select * from table2 were myfld like '%keyword%') union (select * from table3 were myfld like '%keyword%') union (select * from table4 were myfld like '%keyword%') union (select * from table5 were myfld like '%keyword%')
4:02 am on Sept 11, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


But if it's not in table (the first table), that query won't return any results.


Unless I'm missing something, that's not true. One of the advantages of a left join is that is will return results whether or not there's a matching record in the joined table. so

select * from table left join table 2 on table.id=table2.rid=table1.rid
where table1.field='val' or table2.field='val'

even if there's no "table.id=table2.rid=table1.rid" - that is, a null on the join - it would return a row id a found record is in table 1. If there's no found record in table 1 but there *is* one in table2, it will return a record.

I have always been under the impression that using UNION is faster and can be considered optimized if you use it over a bunch of OR statements.


I can't contest this with any real evidence, but a nested select is still another select. :-) Any select has to select from the entire table, an or . . should not.
11:40 am on Sept 12, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 15, 2010
posts: 126
votes: 0


Thanks. Actually my case is different, There is no parent/child relation in these 5 tablse as all these 5 are having the same level and same fields an i can't convert these in one so i am using union and can't use join and taking 5 to 8 hours time to map my each xml feed record with my inventory.
Any suggestion to improve.
1:33 am on Sept 13, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


One thing to speeds things up is to use UNION ALL rather than just UNION if your database supports it.
If you just use UNION, it will try and remove any duplicates, and that creates extra overhead and hence takes longer.

However due to you doing a wild card search with your like '%keyword%', it isn't able to use indexes effectively and is probably having to do a full table scan of each table to try and find matching records.
3:44 pm on Sept 13, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


If there's no found record in table 1 but there *is* one in table2, it will return a record.

I believe that would be a RIGHT JOIN.