Slow advanced products search with MySQL.

Hi all,

I hope you 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
)

Code (markup):

 

Solved

Indexes Added to the option_group and value col's and SQL changed to...

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
        )

Code (markup):