Page 1 of 1

Indexes

Posted: Thu Mar 15, 2007 12:04 pm
by GeXus
I have a table that has indexes set for a couple columns, but when I run explain on a query, it shows under "extra" only 'using where', shouldnt it also show 'using index'?

Posted: Thu Mar 15, 2007 12:15 pm
by GeXus
Okay, looks like it was because i was using 'like', which doenst use indexes.... i've found regexp to be a good alternative

Posted: Thu Mar 15, 2007 12:51 pm
by mikeq
using index; is not maybe what you think it is.

When it shows "using index" the query is getting all the data from the index file without needing to go the data file. Doesn't mean it is using the index to perform the query.

In the explain plan the columns "Possible Keys" and "Key" will show you which could be used and which was used.

It will only "using index" when fields in your query (where clause and select clause) are defined in a multi-column index and they are of integer type (this is for MyISAM), do a google search for "Covering Indexes" for more info.

"Using Index" is good for performance

Posted: Thu Mar 15, 2007 12:55 pm
by mikeq
GeXus wrote:Okay, looks like it was because i was using 'like', which doenst use indexes.... i've found regexp to be a good alternative
Oh and "like" can use indexes, as long as the first character in the search value is not a %

Code: Select all

SELECT * FROM table1 WHERE field1 LIKE 'The Search%';
Would use an index if one was created on field1 (there are exceptions to this, it is based on what the optimizer thinks is best, things like the distribution of values within the table)

Code: Select all

SELECT * FROM table1 WHERE field1 LIKE '%Search%';
In this situation it definitely cannot use the index and would need to do a full table scan, for obvious reasons.

Posted: Thu Mar 15, 2007 8:09 pm
by GeXus
Yeah, I was using 'The Search%' yet it wasnt showing indexes being used, when i switched to the regexp ^The Search, it showed it being used.

mikeq wrote:
GeXus wrote:Okay, looks like it was because i was using 'like', which doenst use indexes.... i've found regexp to be a good alternative
Oh and "like" can use indexes, as long as the first character in the search value is not a %

Code: Select all

SELECT * FROM table1 WHERE field1 LIKE 'The Search%';
Would use an index if one was created on field1 (there are exceptions to this, it is based on what the optimizer thinks is best, things like the distribution of values within the table)

Code: Select all

SELECT * FROM table1 WHERE field1 LIKE '%Search%';
In this situation it definitely cannot use the index and would need to do a full table scan, for obvious reasons.