Indexes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Indexes

Post 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'?
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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.
Post Reply