mysql query taking 30 seconds to execute...too long

Question

I'm trying to run a MySQL query as a search on a site and it is just taking a long time.

I ran it directly from phpMyAdmin and it took around 20 seconds to complete.

Here is the query I am running:

SELECT * , bb_business.id AS bid
FROM bb_business
LEFT OUTER JOIN bb_offers ON bb_business.id = bb_offers.store_id
LEFT OUTER JOIN bb_cat_business ON bb_business.id = bb_cat_business.store_id
LEFT OUTER JOIN bb_categories ON bb_categories.id = bb_cat_business.cat_id
WHERE bb_business.active = '1'
AND (
bb_business.business_name LIKE '%altas%'
OR bb_categories.category_name LIKE '%altas%'
OR bb_business.business_name LIKE '%juice%'
OR bb_categories.category_name LIKE '%juice%'
)
AND (
bb_business.city LIKE '%%'
OR bb_business.state LIKE '%%'
OR bb_business.zip LIKE '%%'
)
GROUP BY bb_business.business_name
ORDER BY bb_business.business_name DESC 
LIMIT 1 , 10`

Speed: Showing rows 0 - 0 (1 total, Query took 20.1130 sec)

I'm not sure the best way to show you the structure. But here a copy and paste for the collation of some of the tables:

bb_ach            128 InnoDB latin1_swedish_ci 32.0 KB - 
bb_business       2,862 InnoDB latin1_swedish_ci 1.6 MB - 
bb_categories     15 MyISAM utf8_general_ci 6.7 KB 60 Bytes

bb_cat_business   2,926 MyISAM utf8_general_ci 49.6 KB 17 Bytes

bb_emails         13 MyISAM utf8_general_ci 6.0 KB - 
bb_member         409 MyISAM utf8_general_ci 62.7 KB - 
bb_member_card    593 MyISAM utf8_general_ci 112.1 KB - 
bb_merchant       154 InnoDB latin1_swedish_ci 32.0 KB - 
bb_offers         80 InnoDB latin1_swedish_ci 16.0 KB - 
bb_products       2 InnoDB latin1_swedish_ci 32.0 KB 

If I can provide anything else. Please let me know.

Thank you so much for any help.

EDIT##################################

Okay, using OMG Ponies suggestion, I altered a few things. Made some stuff MyISAM and created a few FULLTEXT indexes to use.

I ended up with this query:

SELECT *, bb_business.id AS bid FROM bb_business 
LEFT OUTER JOIN bb_offers ON bb_business.id = bb_offers.store_id 
LEFT OUTER JOIN bb_cat_business ON bb_business.id = bb_cat_business.store_id 
LEFT OUTER JOIN bb_categories ON bb_categories.id = bb_cat_business.cat_id 
WHERE bb_business.active = '1' AND 
MATCH(bb_business.business_name) AGAINST ('".addslashes($business)."') OR 
MATCH(bb_categories.category_name) AGAINST ('".addslashes($business)."') OR 
MATCH (bb_business.city,bb_business.state,bb_business.zip) AGAINST ('".addslashes($city)."') 
GROUP BY bb_business.business_name 
ORDER BY bb_business.imported,".$this->sortCol." ".$this->sortDirection." LIMIT ".$this->start.",".$this->limit

I think it made it even slower ;)

The EXPLAIN gives this:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
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 

So the result is that the first query take up to 40 seconds to complete. If I search the exact same phrase again, the speed is as I would expect MYSQL to perform. In the milliseconds. It's just the first search that takes FOREVER...

Any more thoughts? I'd be happy to pay someone to help out.

Answer 1

Taking the query at face value, here's my re-write:

   SELECT *, bb_business.id AS bid
     FROM bb_business b
LEFT JOIN bb_offers o ON o.store_id = b.id
LEFT JOIN bb_cat_business cb ON cb.store_id = b.id
LEFT JOIN bb_categories c ON c.id = cb.cat_id
    WHERE b.active = '1'
      AND (    bb_business.business_name LIKE '%altas%'
            OR bb_business.business_name LIKE '%juice%'
            OR MATCH(c.category_name) AGAINST ('altas', 'juice'))          
 GROUP BY b.business_name
 ORDER BY b.business_name DESC 
    LIMIT 0, 10

• LIMIT starts at zero - starting at one will omit the first record.
• You can use Full Text Search (FTS) functionality on MyISAM tables, which is what the MATCH() .. AGAINST is. You can make it faster by adding a Full Text Index...
• SELECT * is a bad approach - better to spell out every you actually want

Answer 2

Use EXPLAIN to see what is going on, how the database is executing your database.

LIKE will kill performance, the database can't use any index when your parameter starts with %.

Answer 3

Just wanted to come back and revist this and post an answer. I ended up creating a index table and created a PHP script to index the main database. The bottom line was that MySQL did not do a good job handling multiple matches across multiple tables.