GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

query taking too long

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • query taking too long

    I have a problem in a query on my website. whenever this query is performed it takes like 30 seconds to show the results on the website.
    i used mysql admin and found the query with the problem. it takes all of the CPU load and even the server stops responding for seconds. im using mac G4 so i thought this shouldnt be a prblem.

    this is the query

    select * from products where unique_id in (select distinct itemid from item_classification where (classb=207 or classb=211 or classb=234 or classb=221 or classb=237 or classb=240 or classb=239 or classb=250) and (classc=302 or classc=334 or classc=360 or classc=331 or classc=470 or classc=421 or classc=999) )


    table (products) has about 2000 records. also the same in table (item_classification). unique_id is indexed. i tried indexing fields (classb) and (classc) but it didnt help.

    if you can help me with a tip or an advice i'll be thankful.
    thanx.

  • #2
    I know MySQL has poor subselect performance. You may find these threads helpful:

    http://forum.mysqlperformanceblog.com/s/m/1279/
    http://forum.mysqlperformanceblog.com/s/m/2323/

    Comment


    • #3
      thanx for the threads.
      but i was thinking of using inner join as i read it is faster.. can i use inner join in this case to gain better performance?

      Comment


      • #4
        Using the inner join should help. I believe the way it is written now, it is a correlated subquery, meaning that the outer query is re-evaluated for every record the sub-query would return. From the looks of it, I believe the query could be re-written like this:

        SELECT * FROM products AS p INNER JOIN item_classification AS i ON p.unique_id = i.itemid WHERE (i.classb=207 or i.classb=211 or i.classb=234 or i.classb=221 or i.classb=237 or i.classb=240 or i.classb=239 or i.classb=250) and (i.classc=302 or i.classc=334 or i.classc=360 or i.classc=331 or i.classc=470 or i.classc=421 or i.classc=999)

        Hopefully this helps!

        Comment


        • #5
          First, dont use a subselect:


          SELECT products.*FROM products INNER JOIN item_classification ON (products.unique_id = item_classification.itemid)WHERE ( 0 OR classb=207 OR classb=211 OR classb=234 OR classb=221 OR classb=237 OR classb=240 OR classb=239 OR classb=250) and ( 0 OR classc=302 OR classc=334 OR classc=360 OR classc=331 OR classc=470 OR classc=421 OR classc=999)

          Second: in this query, MySQL can not use an Index, because of the or,.. instead use IN and an index (classb,classc)


          SELECT products.*FROM products INNER JOIN item_classification ON (products.unique_id = item_classification.itemid)WHERE 1 AND item_classification.classb IN ( 207, 211, 234, 221, 237, 240, 239, 250 ) AND item_classification.classc IN ( 302, 334, 360, 331, 470, 421, 999 )

          Comment

          Working...
          X