Slow advanced search on MySQL.

[a.stilliard]

Hi all,

 

I hope someone can help with a problem I am having with slow search on MySQL.

 

I have a store which will hold around 50,000 products in a products table. Each product will have 14 options, giving 700,000 options in total. These are held in an options table which is joined via the product id.

 

Users search for products based on the options via an Advanced Search menu.

 

The users need to be able to select multiple options upon which to query.

I would normaly use a JOIN if it was just the one option to select upon, but because its a variable number i thought it would be best to loop through the WHERE EXISTS statement.

 

The issue i have currently is that the query is taking a minimum of 18 seconds (And that was a query when the tables only had a fraction of the total products in).

If you can help us speed this up, or suggest an alternative idea that would be greatly appreciated.

 

Here is the basis of the SQL code im currently using.

(Showing an example of just 2 options selected so my code loops the sql for the EXISTS statement twice. )

 

 

SELECT p.id FROM products p 
WHERE EXISTS (
    
    SELECT op.option_id    
    FROM options op
    WHERE op.product_id = p.id     
    AND op.option_group = 'Tread'
    
    AND CASE op.search_type
        WHEN 'more-than' THEN ( op.option_desc >= 25 )
        WHEN 'less-than' THEN ( op.option_desc <= 25 )
        WHEN 'range-array' THEN ( op.option_desc LIKE '%25%' )    
        ELSE ( op.option_desc = '25' )
    END     
    GROUP BY op.product_id
    LIMIT 0, 1
)
AND EXISTS (
    
    SELECT op.option_id    
    FROM options op
    WHERE op.product_id = p.id     
    AND op.option_group = 'Hole Diameter'
    
    AND CASE op.search_type
        WHEN 'more-than' THEN ( op.option_desc >= 6.3 )
        WHEN 'less-than' THEN ( op.option_desc <= 6.3 )        
        WHEN 'range-array' THEN ( op.option_desc LIKE '%6.3%' )            
        ELSE ( op.option_desc = '6.3' )
    END 
    GROUP BY op.product_id
    LIMIT 0, 1
)

[zeodragonzord]

First of all, do you have indexes on your tables?  Apply indexes to the fields you'll search by.  Is searching with options optional?  If so, you can build the INNER clause to the option table only if the user selects an option with the search.

 

User selects a product in the search

SELECT * FROM products WHERE .....

 

User selects a product and an option in the search

SELECT * FROM products INNER JOIN options ON products.product_id = options.product_id WHERE ...

 

The INNER JOIN will enforce that the product_id will need to match in both tables for it to show up in the result.

[a.stilliard]

Cheers zeodragonzord,

I have the code limiting already when the users don't select options,

but the INNER JOIN works much better cheers.

And im reviewing my INDEX's on the tables thanks.

 

Much faster query now.

 

Also incase anyone else had similar troubles, improved the query further with help from Alex @ http://explainextended.com/

 

SELECT  p.id
FROM    (
        SELECT  product_id
        FROM    options op
        WHERE   op.option_group = 'Tread'
                AND
                CASE op.search_type
                        WHEN 'more-than' THEN ( op.option_desc >= 25 )
                        WHEN 'less-than' THEN ( op.option_desc <= 25 )
                        WHEN 'range-array' THEN ( op.option_desc LIKE '%25%' )    
                        ELSE ( op.option_desc = '25' )
                END
        GROUP BY
                product_id
        ) opm
JOIN    products p
ON      p.id = opm.product_id
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    options op
        WHERE   op.product_id = p.id     
                AND op.option_group = 'Hole Diameter'
                AND
                CASE op.search_type
                        WHEN 'more-than' THEN ( op.option_desc >= 6.3 )
                        WHEN 'less-than' THEN ( op.option_desc <= 6.3 )
                        WHEN 'range-array' THEN ( op.option_desc LIKE '%6\.3%' )    
                        ELSE ( op.option_desc = '6.3' )
                END
        )