Why is MySQL so slow when using JOIN instead of WHERE?

Question

I have two tables:

CREATE TABLE `test_sample` ( `idtest_sample` varchar(50) NOT NULL, `test_samplecol` varchar(45) DEFAULT NULL, UNIQUE KEY `idtest_sample_UNIQUE` (`idtest_sample`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 

and

 CREATE TABLE `new_table` ( `idnew_table` int(11) NOT NULL, UNIQUE KEY `idnew_table_UNIQUE` (`idnew_table`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 

The first table contains 5 million records, and the second contains only 10 records.

The duration of this request is more than 5 seconds:

 SELECT * FROM test_sample INNER JOIN new_table ON test_sample.idtest_sample = new_table.idnew_table 

while this request is being executed immediately (less than 0.001 seconds):

 SELECT * FROM test_sample WHERE test_sample.idtest_sample IN ('3','1597','25963','170596','196485', '545963','999999','1265896','1569485','1999999') 

Why does the first request take so long?

Answer 1

Have you tried to see the execution path? DESC {SQL}, your first, of course, is longer

The first query necessarily displays all 5 million rows for each row of another table. Although your second query only searches for a specific identifier that has an index (since it is part of the primary key)

Edit: Here is your EXPLAIN (simplified):

 +----+-------------+-------------+--------+----------------------+------+ | id | select_type | table | type | possible_keys | key | +----+-------------+-------------+--------+----------------------+------+ | 1 | SIMPLE | new_table | system | idnew_table_UNIQUE | NULL | | 1 | SIMPLE | test_sample | ALL | idtest_sample_UNIQUE | NULL | +----+-------------+-------------+--------+----------------------+------+ +----+-------------+-------------+-------+----------------------+----------------------+ | id | select_type | table | type | possible_keys | key | +----+-------------+-------------+-------+----------------------+----------------------+ | 1 | SIMPLE | test_sample | const | idtest_sample_UNIQUE | idtest_sample_UNIQUE | +----+-------------+-------------+-------+----------------------+----------------------+ 

As you can see, in the table test_sample there is a row "ALL" (5 million rows)

You can look here: http://hackmysql.com/case4

Answer 2

the first query will take longer because it checks each row of another table for 5 million records. on the other hand, the second sql has a specific indexed column. Hope this helps you.