CODEWITHSUNDEEP
mysql
number50
number50

Reputation: 153

why Explain shows 'using where' instead of 'using index' on indexed column?

I am using mysql and my table type is myisam with 100 million records.

This table have 90 columns and indexes added on 30 columns.

I run this query "select id from tablename where id between num1 and num2". This results in almost 10 seconds.
Now I add in where condition date like, "select id from tablename where id between num1 and num2 and date between date1 and date2". And it results in 4 minutes.

'date' field is also indexed.

In first case in Explain, it shows "using index" in "extra" column, while in second case in Explain, it shows "using where" in "extra" column.

I don't understand the reason for different behaviour of Explain, and difference in time taken for query.
Please anyone explain me these, so that I can work in right direction for optimizing query.

Upvotes: 1

Views: 626

Answers (2)

Vatev
Vatev

Reputation: 7590

select id from tablename where id between num1 and num2

This can be resolved from the id index only, without touching the table at all. That is what using index means.

select id from tablename where id between num1 and num2 and date between date1 and date2

MySQL will get the internal ROWIDs from the id index, then go to the actual table and fetch all the corresponding rows, so it can check if the date is between date1 and date2.

Upvotes: 2

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Is there an index that includes both columns, id and date in that order? If not, create one. Indexes are used most efficiently when they closely match the where condition (or otherwise: match your where conditions to your indexes).

If you have an index on the id column and the where clause only uses that column, the index can be used efficiently.

If you have two columns in the where condition and no matching index is found, a full table scan is performed.

Upvotes: 2

Related Questions