homepage Welcome to WebmasterWorld Guest from 23.23.12.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
select from multiple tables
select from multiple tables
fahad direct



 
Msg#: 4199964 posted 1:13 pm on Sep 10, 2010 (gmt 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?

 

rocknbil

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



 
Msg#: 4199964 posted 7:12 pm on Sep 10, 2010 (gmt 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';

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4199964 posted 8:39 pm on Sep 10, 2010 (gmt 0)

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

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



 
Msg#: 4199964 posted 10:08 pm on Sep 10, 2010 (gmt 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.

fahad direct



 
Msg#: 4199964 posted 1:34 am on Sep 11, 2010 (gmt 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%')

rocknbil

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



 
Msg#: 4199964 posted 4:02 am on Sep 11, 2010 (gmt 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.

fahad direct



 
Msg#: 4199964 posted 11:40 am on Sep 12, 2010 (gmt 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.

Dijkgraaf

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4199964 posted 1:33 am on Sep 13, 2010 (gmt 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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4199964 posted 3:44 pm on Sep 13, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved