Slow performance of MySQL Join Query

Question

I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the search go from <1sec to 50sec...

Here is what's happening:

When a user searches for products in table (a), I have to check whether any seller has this user on his allowed-list and whether he is assigned a pricelist. If so, I have to also select the pricelist price from the the pricelist table (p).

I'm pre-compiling this allowed-list check as a Coldfusion query, which returns pricelist_name and sellerID into the variable_pricelists for the current user.

This will look like:

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR 
...

My actual search:

<cfquery datasource="db" name="all_art">
 SELECT count(a.id) AS total, a.nos, a.nos_anzeige
 FROM arts a
 // check for pricelist
 <cfif variables.pricelists neq "">
 LEFT JOIN pricelists p ON
 a.ean = p.ean 
 AND a.iln = p.iln
 AND p.price != 0
 AND ( #variables.pricelists# ) 
 </cfif>
 // 2nd join
 <cfif s_farbe neq "">
 LEFT JOIN farbenzuordnung zu 
 ON a.farbe = zu.farbe 
 </cfif>
 WHERE a.aktiv = "ja"
 ... more criteria...
 GROUP BY a.iln, a.artikelnummer, a.preis_aktuell, a.artikelbezeichnung
 HAVING sum(a.bestand) != 0 OR (a.nos = "ja" AND a.nos_anzeige = "ja") 
</cfquery>

The pricelist table contains cols

 ILN/SellerID EAN Currency Price Name_pricelist

So for products from sellers, which have a pricelist which is assigned to the user, I also need to select the pricelist price and currency to have these available once I build the result set.

Question:
How can I speed up the search. I need to run it a lot, so 50sec is out of the quesiton, when using pricelists.

I'm not sure I really need a LEFT JOIN at all, when I'm only selecting prices from the other table on matching criteria and do not want to add any records from B to the selection, because they then will be in there twice.

I have been told indexing would be a possibility but since I'm in my first month of MySQL I'm not really sure where to start. Avoiding the LEFT JOIN altogether would be nice.

So, thanks for any tips you can provide!

EDIT:
Ok, I EXPLAINED the query. This is what I get and what doesn't tell me

id select table type possible_keys key key_len ref rows extra
1 simple a ref i_aktiv i_aktiv 7 const 13001 using where/temp/filesort
1 simple p all NULL NULL NULL NULL 22799 
1 simple zu index NULL primary 52 NULL 53 using index

The 2nd entry is my LEFT JOIN in question. Can someone shed some light on what this means?

• ypercubeᵀᴹ: Add an EXPLAIN output and the tables definitions (SHOW CREATE TABLE tableName)
• frequent: where do I add this?
• ypercubeᵀᴹ: Edit the question and add it in the end.
• frequent: can't get it to work from Coldfusion and since I'm working remote on the MySQL database I don't have edit privileges... any idea how to get the info, I'm looking for?
• ypercubeᵀᴹ: Don't you have direct CLI access (Command Line) or PHPMyAdmin?

Answer 1

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

• frequent: Thanks for the answer and help so far! I found the table had no index at all... I added one and went from scanning 60k+ rows to 23. I will post the show create sometime today.