Indexes
Posted: Thu Mar 15, 2007 12:04 pm
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'?
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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%';
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%';