Mysql – Slow performance of MySQL Join Query

join;MySQLperformancequery-performance

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?

Best Answer

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.

Related Question