CODEWITHSUNDEEP
mysqldatabaseindexing
Juan Pablo B
Juan Pablo B

Reputation: 515

Query with where is not use index

I have two tables, users and products. In users there are 4 users and products has a fk to users (column user_id).

In products there are 2395288 records of which 765277 are with user_id = 4.

Without index if I do

explain select * from products where user_id = 4;

I get

enter image description here

So, I added an index

CREATE INDEX userid ON products(user_id)

After that, I executed again but the index doesn`t work:

explain select * from products where user_id = 4;

enter image description here

Only works if I do select count(*) or select user_id

enter image description here

But I need the index to select * or select name, description to products where user_id = x

Upvotes: 0

Views: 33

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

In general, MySQL's optimizer believes that if you search for a value that is common in that column, for example more than about 20% of the rows match the value, then it decides not to use the index, because looking up values in an index and then doing another lookup to get the row where that value is found is more work.

The 20% threshold is not an official breakpoint, it's just something I've observed.

If you think the optimizer is making a wrong choice, you can give it an index hint to persuade it that a table-scan is much more costly.

select ... from products FORCE INDEX(userid) where user_id = 4;

I try to use index hints only if necessary. Usually the MySQL optimizer makes the right choice without a hint. Also using an index hint means my query now is tied to that index, so if I want to drop the index someday, I need to change the query first.

Upvotes: 2

Related Questions