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_bus开发者_开发技巧iness.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!
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).
- 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)... - 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)...
精彩评论