MySQL Forums
Forum List  »  Performance

Re: Adding keyword search (fulltext) to query
Posted by: Rick James
Date: October 26, 2012 09:17PM

And the EXPLAIN for the original query?
I'll guess that it starts with description_test, then moves on to listings.
With 319K rows with 'swimming pool'...
1. Look up the fields needed from description_test (title and description, id) - 319K lookups
2. JOIN to listings -- another 319K lookups
3. filter down to 549 (or fewer) rows
4. JOIN to the other tables, etc.

What we need to do is avoid the 3*319K lookups.
But, we have a potential problem -- the numbers could be reversed. That is, if the FULLTEXT lookup found only a few hundred rows, the query would be efficient, and if the filtering on `listings` was weaker (eg, not down to the city), starting with `listings` may not be the best.

There is no simple way out of this dilemma.

This will help, and be moderately robust:
Please time this:
SELECT  id
    FROM  ( 
              ( SELECT  listing_id AS id
                    FROM  description_test
                    WHERE  MATCH (`descriptions_test`.`description`)
                           AGAINST ('swimming pool' IN BOOLEAN MODE)
                )
        UNION
              ( SELECT  id
                    FROM  listings
                    WHERE  `listings`.`category` = 1
                      AND  `listings`.`city` = 35862
                      AND  `listings`.`visible` = 1; ) )
    GROUP BY  id
    HAVING  COUNT(*) = 2;
If that runs fast enough, then make that a subquery in
SELECT  ...
    FROM  ( that subquery ) x
    JOIN  listings ON listings.id = x.id
    JOIN  ...the rest of the tables... 

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Adding keyword search (fulltext) to query
901
October 26, 2012 09:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.