homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

My SQL query is crawling along- why?

5+ Year Member

Msg#: 4078238 posted 11:23 am on Feb 11, 2010 (gmt 0)

I've got a query, below that's taking ages to bring back results. I think I've done something wrong, but am not sure.

It uses 3 tables, offers, products and tags

I've created 4 table aliases for tags so I can query them. Any help on this would be very welcome.

SELECT p.product_name, p.page, p.description, p.brand_name, p.model,
t1.tag, t2.tag,t3.tag,t4.tag

FROM offers o JOIN products p
ON o.product_name = p.product_name
JOIN tags t1
ON t1.product_name = p.product_name
JOIN tags t2
ON t2.product_name = p.product_name
JOIN tags t3
ON t3.product_name = p.product_name
JOIN tags t4
ON t4.product_name = p.product_name
WHERE p.cat1 = 'product category'
AND o.display_price > 5 AND t2.tag = 'some feature' AND t3.tag = 'another feature' GROUP BY o.product_name ORDER BY p.size ASC LIMIT 0, 10



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

Msg#: 4078238 posted 4:29 pm on Feb 11, 2010 (gmt 0)

Do you have the tables properly indexed?

I assume product_name is a VARCHAR in each table? You'll likely have better results in using an INT product_id instead of a VARCHAR product_name. You'll also avoid a major headache in the future if the name of a product changes.


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

Msg#: 4078238 posted 6:46 am on Feb 12, 2010 (gmt 0)

Agreed, joining on a textual field is much slower and it's killing you, and is likely to get worse as it grows.

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