How do I optimise this mysql query?

SELECT MAX( CAST( auction_price.Auctionprice AS SIGNED ) ) AS `max_au开发者_C百科ction_price`
FROM auction_detail
LEFT JOIN auction_price ON auction_detail.auction_id = auction_price.auction_id
WHERE auction_detail.season = '2011'
AND auction_detail.sale_no = '26'
AND auction_detail.area LIKE '%Mumbai%'
AND auction_detail.broker_code LIKE '%PAP%'
AND auction_detail.category LIKE '%CTC%'
AND auction_detail.tea_type LIKE '%BEST%'

The above query takes about 49secs to run, so I really need to optimize it for performance. Any suggestions?

run the command in mysql prefixed with explain, it will show you what indexes are being used in the query, it'll take a bit to learn how to read the output but it's not too hard.

EXPLAIN SELECT MAX( CAST( auction_price.Auctionprice AS SIGNED ) ) AS `max_auction_price`
FROM auction_detail
LEFT JOIN auction_price ON auction_detail.auction_id = auction_price.auction_id
WHERE auction_detail.season = '2011'
AND auction_detail.sale_no = '26'
AND auction_detail.area LIKE '%Mumbai%'
AND auction_detail.broker_code LIKE '%PAP%'
AND auction_detail.category LIKE '%CTC%'
AND auction_detail.tea_type LIKE '%BEST%'

Are the LIKE keywords words that always stand on their own (as opposed to parts of strings)? (Mumbai, PAP, CTC, BEST)

If so, give full-text search a try. A properly indexed full-text search might be faster than LIKEs.

Create indexes.

for example

• on auction_detail.sale_no and on auction_price.auction_id.

OR

• on auction_detail.area and on auction_price.auction_id and query like 'Mumbai%' (that is, without %)

• There is no point to use LEFT join in this particular case. Use INNER join;
• Create a composite index (sale_no, season).