query taking too long

MySQL & MariaDB Other MySQL® Questions
1

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:

[URL]Percona Community Forum - Technical forum for help with Percona's open source software for MySQL®, PostgreSQL, and MongoDB® databases.
[URL]Percona Community Forum - Technical forum for help with Percona's open source software for MySQL®, PostgreSQL, and MongoDB® databases.

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?

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!

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 )