Welcome to WebmasterWorld Guest from 54.226.189.112

Forum Moderators: open

Message Too Old, No Replies

select from multiple tables

select from multiple tables

     

fahad direct

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

5+ Year Member



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?

rocknbil

7:12 pm on Sep 10, 2010 (gmt 0)

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



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';

LifeinAsia

8:39 pm on Sep 10, 2010 (gmt 0)

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



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?

Demaestro

10:08 pm on Sep 10, 2010 (gmt 0)

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



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.

fahad direct

1:34 am on Sep 11, 2010 (gmt 0)

5+ Year Member



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%')

rocknbil

4:02 am on Sep 11, 2010 (gmt 0)

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



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.

fahad direct

11:40 am on Sep 12, 2010 (gmt 0)

5+ Year Member



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.

Dijkgraaf

1:33 am on Sep 13, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

LifeinAsia

3:44 pm on Sep 13, 2010 (gmt 0)

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



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month