Indexes
Moderator: General Moderators
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
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
Oh and "like" can use indexes, as long as the first character in the search value is not a %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
Code: Select all
SELECT * FROM table1 WHERE field1 LIKE 'The Search%';
Code: Select all
SELECT * FROM table1 WHERE field1 LIKE '%Search%';
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:Oh and "like" can use indexes, as long as the first character in the search value is not a %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
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 'The Search%';
In this situation it definitely cannot use the index and would need to do a full table scan, for obvious reasons.Code: Select all
SELECT * FROM table1 WHERE field1 LIKE '%Search%';