MySQL: Why Select .. IN with subquery could not use index

user_2612912 0
Viewed 296

I started to learn MySQL and facing some issues regarding indexing for subquery or join. I have two tables created as following

create table User(id integer, poster integer, PRIMARY KEY (id,poster));
insert into User(id, poster) values(1, 123);
insert into User(id, poster) values(1, 345);
insert into User(id, poster) values(2, 123);


create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),  INDEX(poster),INDEX(time,c));
insert into Feed(id, poster, c,time) values(1, 123, 0, 2);
insert into Feed(id, poster, c,time) values(2, 123,1,1);
insert into Feed(id, poster, c,time) values(3, 345,2,3);

I initially tried some simple queries like 

1. Select poster from User where id =1;  
2. Select c from Feed where poster = 1;
3. Select c from Feed where poster in (1,2,3)

The third query explain looks like 

SIMPLE  Feed    NULL    ALL poster  NULL    NULL    NULL    3   100.00  Using where; Using filesort

I am not sure why it requires file sort. However after add a composite index INDEX(time,poster,c)to Feed table.Same query will use index

Here is new create table query

   create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),INDEX(time,poster, c));

Here is explain output with new composite index 1 SIMPLE Feed NULL index NULL time 15 NULL 3 50.00 Using where; Using index

My guess is since order by has higher priority and it is the leftmost index, so we used it first. Then by add poster into composite index, we will be able to still use this composite index to do filter, and finally return c.

Then I tried some subquery 

explain SELECT Feed.c from Feed where Feed.poster IN(select poster from User where id =1) order by Feed.time;

Nothing fancy here, I just replace hardcoded (1,2,3) with subquery. I expect to see same explain result, but instead I get 

1   SIMPLE  User    NULL    ref PRIMARY,poster  PRIMARY 4   const   1   100.00  Using index; Using temporary; Using filesort
1   SIMPLE  Feed    NULL    index   NULL    time    15  NULL    3   33.33   Using where; Using index; Using join buffer (Block Nested Loop)

I am curious why USER table has Using temporary; Using filesort. I also tried left join it also has same explain output

explain SELECT Feed.c
FROM `Feed` 
LEFT JOIN `User` on User.poster = Feed.poster where User.id = 1 order by Feed.time;

Based on my reading, we should avoid using filesort and temporaray file.

How can I optimize my indexing and queries?

Thanks

indexing mysql sql database
1 Answers

It's not that it can't, it's that there is no benefit.

An index is a bit like another table that can be joined on to first, to help with the join on to the real table.

In your case, it's quicker to scan the table. The alternative would be to use the index to isolate which row(s) in the underlying table are required and then go to the underlying table to get those rows.

That would be different if your table was a million rows long. Then it would be worth the effort of using the index, to reduce the effort in scanning the table.

So, write a testbed that creates a LOT more random data, then you'll be able to see it.


Alternatively, use a covering index. One that holds all the columns you need to search AND all the columns you'll include in SELECTs and JOINs.

In the example below I change (for table Feed) INDEX(poster) to INDEX(poster, c). Now, if the query planner reads from the index, it immediately knows the value of c too, without "joining" on to the underlying table.

create table User(id integer, poster integer, PRIMARY KEY (id,poster), INDEX(poster));
insert into User(id, poster) values(1, 123);
insert into User(id, poster) values(1, 345);
insert into User(id, poster) values(2, 123);

create table Feed(id integer, poster integer, c integer, time integer, PRIMARY KEY(id),  INDEX(poster, c),INDEX(time,c));
insert into Feed(id, poster, c,time) values(1, 123, 0, 2);
insert into Feed(id, poster, c,time) values(2, 123,1,1);
insert into Feed(id, poster, c,time) values(3, 345,2,3);

Now, compare two queries...

Select c from Feed where poster in (1,2,3)

SELECT c, time FROM feed WHERE poster IN (1,2,3)

The first can be answered by just the index.

The second needs either to scan the whole table or seek on the index AND join on to the table. Because the table is so small, the optimiser will decide just to scan the whole table, as that will be cheaper.

Dems_533410
Related