views:

40

answers:

0

Hello all

I'm trying to optimize this mysql query using EXPLAIN. Can somebody please help me out over here?

EXPLAIN SELECT * FROM keyword
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99

Basically I want to find out the keywords which start with "some keyword" but don't exactly match it, and whose price is not in some particular range. And oh, I have to get them in descending order of price (which is causing the problem here).

Explain Output:

id: 1
select_type: SIMPLE
table: keyword
type: range
possible_keys: PRIMARY, keyword_price, price_keyword
key: keyword_price
key_len: 765
ref: NULL
rows: 24
Extra: Using where; Using filesort

Indexes Key_name : column_names

PRIMARY: keyword
keyword_price: keyword, price
price_keyword: price, keyword

Now if I give the hint to use index, and change the query to

EXPLAIN SELECT * FROM keyword USE INDEX (price_keyword)
WHERE keyword LIKE "panasonic%"
AND keyword != "panasonic"
AND price < 3230 AND price > 3370
ORDER BY price DESC
LIMIT 99

Explain Output changes to

id: 1
select_type: SIMPLE
table: keyword
type: index
possible_keys: price_keyword
key: price_keyword
key_len: 790
ref: NULL
rows: 1043044 (WHAT THE ????)
Extra: Using where

The explain output shows the number of rows has increased exponentially but the "Using filesort" has gone.

Which query is better in this case? Can that "rows examined" column be deceptive?

Regards