Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

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

share|improve this answer
    
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 Jul 24 '12 at 6:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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