NATURAL JOIN WHERE IN RESERVATIONS

Question

Recently, I was engaged in extracting a large amount of data, which consists of thousands of records from a MySQL database. Since this was my first time to process such a large dataset, I did not think about the effectiveness of the SQL statement. And the problem arises.

Here are the database tables (This is just a simple curriculum database model):

course:

+-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | course_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | lecturer | varchar(20) | NO | | NULL | | | credit | float | NO | | NULL | | | week_from | tinyint(3) unsigned | NO | | NULL | | | week_to | tinyint(3) unsigned | NO | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 

select:

 +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | select_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | card_no | int(10) unsigned | NO | | NULL | | | course_id | int(10) unsigned | NO | | NULL | | | term | varchar(7) | NO | | NULL | | +-----------+------------------+------+-----+---------+----------------+ 

When I want to get all the courses a student has chosen (with his card number) SQL statement

 SELECT course_id, name, lecturer, credit, week_from, week_to FROM `course` WHERE course_id IN ( SELECT course_id FROM `select` WHERE card_no=<student card number> ); 

But it was very slow, and it did not return anything for a long time. So I changed the WHERE IN clauses to NATURAL JOIN . Here is the SQL,

 SELECT course_id, name, lecturer, credit, week_from, week_to FROM `select` NATURAL JOIN `course` WHERE card_no=<student card number>; 

He returns immediately and works great.

So my question is:

• What is the difference between NATURAL JOIN and WHERE IN Clauses?
• What makes them work differently? (Perhaps because I did not configure any INDEX ?)
• When will we use NATURAL JOIN or WHERE IN ?

Answer 1

Theoretically, two queries are equivalent. I think this is just a bad MySQL query optimizer implementation that makes JOINs be more efficient than WHERE IN. Therefore, I always use JOIN.

Have you looked at the EXPLAIN output for two queries? Here is what I got for WHERE IN :

 +----+--------------------+-------------------+----------------+-------------------+---------+---------+------------+---------+--------------------------+ | 1 | PRIMARY | t_users | ALL | NULL | NULL | NULL | NULL | 2458304 | Using where | | 2 | DEPENDENT SUBQUERY | t_user_attributes | index_subquery | PRIMARY,attribute | PRIMARY | 13 | func,const | 7 | Using index; Using where | +----+--------------------+-------------------+----------------+-------------------+---------+---------+------------+---------+--------------------------+ 

It apparently does a subquery, and then looks at each row in the main table, checking to see if it is turned on - it does not use an index. For JOIN I get:

 +----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+ | 1 | SIMPLE | t_user_attributes | ref | PRIMARY,attribute | attribute | 1 | const | 15 | Using where | | 1 | SIMPLE | t_users | eq_ref | username,username_2 | username | 12 | bbodb_test.t_user_attributes.username | 1 | | +----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+ 

Now it uses an index.

Answer 2

Try the following:

 SELECT course_id, name, lecturer, credit, week_from, week_to FROM `course` c WHERE c.course_id IN ( SELECT s.course_id FROM `select` s WHERE card_no=<student card number> AND c.course_id = s.course_id ); 

Note the addition of the AND clause to the subquery. This is called a companion subquery because it binds the two course identifiers, as does the NATURAL JOIN.

I think the Barmar index description is at a mark.