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# ) 
    // 2nd join
    <cfif s_farbe neq "">
        LEFT JOIN farbenzuordnung zu 
            ON a.farbe = zu.farbe 
    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")   

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?

  • Add an EXPLAIN output and the tables definitions (SHOW CREATE TABLE tableName) Commented Jul 11, 2012 at 14:56
  • where do I add this?
    – frequent
    Commented Jul 11, 2012 at 14:57
  • Edit the question and add it in the end. Commented Jul 11, 2012 at 14:58
  • 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?
    – frequent
    Commented Jul 11, 2012 at 15:14
  • Don't you have direct CLI access (Command Line) or PHPMyAdmin? Commented Jul 11, 2012 at 15:16

1 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

( 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.

  • 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.
    – frequent
    Commented Jul 24, 2012 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.