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.
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!
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?