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.

We are currently in the process of trying to re-write a very large and nasty dynamic search query. The search term for this query was 'red christmas napkins' and these 3 keywords have been added to using other knows synonyms to produce the full list searched against below (the query below is the end result that was ran on MySQL).

As I'm sure you will all notice very quickly there is far too much going on here but I wanted to get advice on how you would best re-write this for sheer query speed? The releveance scores against each individual term being search aren't completely random, they are dynamically calculated for each query based on a large number of factors incase anyone was wondering.

I have also inclded the MySQL responce when running EXPLAIN on the live DB server using the same query as below.

Thanks in advance for any help/insite you can provide.

SELECT p.pid, ((
    (MATCH (p.title)
        AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' 
            IN BOOLEAN MODE)) * 10) + (
    (MATCH (p.code)
        AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' 
            IN BOOLEAN MODE)) * 0.3) + (
    (MATCH (p.description)
        AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' 
        IN BOOLEAN MODE)) * 0.2) + (
    (MATCH (p.search_keywords)
        AGAINST ('red christmas napkin red xmas napkin red christmas napkins red 
            xmas napkins red christmas serviette red xmas serviette red christmas 
            serviettes red xmas serviettes' 
        IN BOOLEAN MODE)) * 5) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%red christmas napkin 
                red xmas napkin red christmas napkins red xmas napkins red christmas 
                serviette red xmas serviette red christmas serviettes red xmas serviettes%' 
                AND pid = p.pid 
            LIMIT 1
    ), 0) * 10) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%red%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.857142857143) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%christmas%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.714285714286) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%napkin%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.571428571429) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%xmas%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.428571428571) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%napkins%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.285714285714) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%serviette%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10.142857142857) + 
    (IFNULL(
        (
            SELECT 10 
            FROM prod 
            WHERE live = 1 AND deleted = 0 AND title LIKE '%serviettes%' AND pid = p.pid 
            LIMIT 1
    ), 0) * 10)
) AS 'relevance'
FROM prod p
LEFT JOIN prod_link_cat ON prod_link_cat.pid = p.pid
LEFT JOIN cat ON prod_link_cat.link_cid = cat.cid
    AND p.pid = prod_link_cat.pid
LEFT JOIN brand ON brand.bid = p.bid
    AND brand.live = 1
    AND brand.deleted = 0
WHERE p.live = 1
    AND p.deleted = 0
GROUP BY p.pid
HAVING relevance > 0
ORDER BY prod_link_cat.position ASC
LIMIT 0, 12

(explain)

id  select_type         table         type          possible_keys               key             key_len ref        rows  Extra 
1   PRIMARY             p             index_merge   live,deleted                deleted,live    1,1     NULL       1171 Using intersect(deleted,live); Using where; Using temporary; Using filesort
1   PRIMARY             prod_link_cat ref           pid                         pid             4       sryg.p.pid  4   
1   PRIMARY             cat           eq_ref        PRIMARY                     PRIMARY         4       sryg.prod_link_cat.link_cid 1   Using index
1   PRIMARY             brand         eq_ref        PRIMARY,live,live_2,live_3  PRIMARY         4       sryg.p.bid  1   
9   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
8   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
7   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
6   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
5   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
4   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
3   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
2   DEPENDENT SUBQUERY  prod          eq_ref        PRIMARY,live,deleted        PRIMARY         4       func        1   Using where
share|improve this question
    
Your explain part is very hard to read. Can you please format that better? –  trygvis Nov 23 '12 at 23:49
add comment

2 Answers 2

up vote 0 down vote accepted

This query shows a few design problems.

You're hitting the same table you're already looking at (prod) with 8 subqueries, primary key to primary key, limit 1 even though you can't match a primary key with an equality comparison more than once, and testing two conditions that can't possibly be false because they're eliminated by the WHERE in the outer query?

The subqueries could be eliminated with a construct like:

IF(p.title LIKE '%red%',108.57,0) + IF(p.title LIKE '%christmas%',107.14,0) /* + etc. */ AS relevance,

...however, I'm not sure how much else can be done to help this query. Mind you, that might help quite a bit, depending on whether the optimizer was catching on to what you were doing and optimizing some of that away, but that doesn't look likely.

I'm also confused about the LEFT JOIN to the brand table, which is probably not doing what you think it's doing.

Perhaps Fulltext Query Expansion could do something useful, here.

share|improve this answer
    
Implemented this change and query times are down to 0.4244 sec VS 2.4169 sec perviously - Still a few changes to go to further refine but a great leap forward - Thanks dezso –  Aran Dec 3 '12 at 11:08
add comment

Remove "deleted" items from the table.

LIKE will probably be slower than MATCH .. '+word' IN BOOLEAN MODE.

LIKE '%red%' will match 'Fred', 'featured', 'registered trademark', 'structured', etc.

REGEXP '[[:<:]]red[[:>:]]' will match the "word" 'red', but will be noticeably slower than LIKE.

"red", unless you have ft_max_word_len = 3 or less, will be ignored

Use EXISTS(SELECT * ...) instead of LIMIT 1. EXISTS will be 0 or 1, so then multiply by the factor you desire.

Create a temp table with the red christmas napkins. Then run a second query, using that, to get the relevance and ordering. This may trick it into using indexes in a different order. Note: MyISAM will always(?) pick FULLTEXT first; sometimes that is not optimal.

Build a sophisticated preprocessor discovers keywords and 'tags' the rows. Then use that table for weighted searching.

None of the above is likely to be a 'magic bullet'; I hope they give you some useful ideas.

share|improve this answer
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.