There are now two tables, table1 and Table2,table1 have 10 million data (ID primary key index), Table2 has three data (UID field 3,5,7);
Select * from where inch Select from table2);
A blink of an eye feels that the sentence should be quick; you might be wishful thinking that you execute the statement inside the parentheses and then execute the outer Select, and the outer select uses the ID primary key speed to fly.
Actually this statement executes very slowly, I test 20s here;
Through explain analysis, this statement does not use the index, but the full table scan; Why?
In fact, the internal MySQL is not according to our ideas to run, he is from the outer execution of the walk, each sweep a row of ID and the inner query comparison, so the outer layer is a full table scan;
Change this statement to:
Select * from where inch 3,5,7 ); "Add that in MySQL internal in will be automatically converted to exists"
The execution time programming millisecond level, through the explain view uses the range scan, can see the MySQL internal operation principle;
And then we'll see if there's a solution:
Select table1. * from Inner Join on table1.id=table2.uid;
The query time is also millisecond-level;
This time through explain found that MySQL first executed the Select UID from table2, then executed select table1 and used Eq_ref one-to-one index;
MySQL in sub-query traps