Revisiting MySQL Multiple Match Query....takes so long

Question

Hi guys,

I'm revising my question from a few days ago. Rewrote my query.

Can someone tell me why running any kind of query on multiple tables takes so long using the query I'll post below?

Is there anyone that has time to help me out with this? I can pay $200.00 I figure that's good for two hours of work. I'll post the answer back on here so it will benefit everyone.

The query is basically this:

SELECT bb_business.business_name, bb_business.id AS bid FROM bb_business
LEFT JOIN bb_offers       ON bb_business.id   = bb_offers.store_id
LEFT JOIN bb_cat_business ON bb_business.id   = bb_cat_business.store_id
LEFT JOIN bb_categories   ON bb_categories.id = bb_cat_business.cat_id
WHERE bb_business.active = '1'
  AND MATCH(bb_business.business_name)   AGAINST ('zebra')
  OR  MATCH(bb_categories.category_name) AGAINST ('zebra')
  OR  MATCH (bb_business.city,bb_business.state,bb_business.zip) AGAINST ('zebra')
GROUP BY bb_business.business_name
ORDER BY bb_business.business_name DESC
LIMIT 1,10

That query takes 50 seconds to execute the first time. The second time it's fast as would be expected.

If I alter that query to only use one Match Against, it is fast. As soon as I add a second MATCH or LIKE statement, it's back up to 40-60 execution times.

Running that exact query runs at: MySQL returned an empty result set (i.e. zero rows). (Query took 47.7614 sec)

Explain returns this:

1 SIMPLE bb_business ALL NULL NULL NULL NULL 2877 Using temporary; Using filesort 
1 SIMPLE bb_offers ALL NULL NULL NULL NULL 94   
1 SIMPLE bb_cat_business ALL NULL NULL NULL NULL 5697   
1 SIMPLE bb_categories eq_ref PRIMARY PRIMARY 8 buxback_site.bb_cat_business.cat_id 1 Using where 

When using only one match, the query uses a fulltext index I have on bb_business. When I have multiple matches, it does not seem to use any index.

Here are the indexes on bb_business:

PRIMARY  PRIMARY 2877       id  
store_id  UNIQUE 2877       store_id  
index_business_name  INDEX 2877       business_name  
business_name  FULLTEXT 1       business_name  
city  FULLTEXT 1       city  
state  
zip  

Here are the indexes on bb_categories:

PRIMARY  PRIMARY 15       id  
category_name  UNIQUE None       category_name  
category_name_2  FULLTEXT None       category_name 

I'm desparate!

Thank!

Answer 1

The problem is that you're forcing MySQL to create temporary tables to resolve the query. That's pretty much going to be an issue so long as you're doing multiple matches.

If you're use case really requires you to do that, you have two main options (that I can see).

1. Create a summary table which is de-normalized where you can put a full-text index on all the columns that you need to match against. Then, keep this table updated (either through triggers, stored procedures, or just diligence when you are executing your queries in code)... Then do all matching against that summary table (it should eliminate the need for having multiple MATCH clauses in one query)...
2. The other option would be to use a dedicated search engine, like Apache SOLR or Sphinx. You would need to search separately and then pull the results back from the database, but those search engines are far more powerful than MySQL (Well, not powerful, but it's easier to do complex queries), and much faster than MySQL for executing those queries. They can do so because they pre-index everyting (they sacrifice disk space -sometimes a lot of it- for speed)...