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).