0
CREATE TABLE `request` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_by` int(11) unsigned DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


explain select * from request r
left join user u on (r.created_by = u.id)

+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                                             |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |r    |NULL      |ALL |NULL         |NULL|NULL   |NULL|2   |100     |NULL                                              |
|1 |SIMPLE     |u    |NULL      |ALL |PRIMARY      |NULL|NULL   |NULL|1   |100     |Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+

Here is the content of the tables:

+--+----------+-------+
|id|created_by|content|
+--+----------+-------+
|1 |NULL      |Test   |
|2 |1         |Bar baz|
+--+----------+-------+


+--+------+
|id|name  |
+--+------+
|1 |Thomas|
+--+------+

Why is it a nested loop ? If I add a condition on join like this: (r.created_by > 0 AND r.created_by = u.id) there is no more nested loop.

My version is MySQL 5.7.34.

6
  • 2
    Probably the optimizer realizes you have so few rows in these tables, it doesn't matter. Each table fits on a single page in the database anyway, so there's no need to do any index lookups. If you had more rows, the optimizer's strategy may be different. When you are testing the query optimizer, you should create at least enough rows to make the table fill a few db pages. Oct 11 at 14:05
  • I have tested with real tables and it's the same. With new condition i passed from 800ms to 50ms, so there is an impact Oct 11 at 15:34
  • Try running ANALYZE TABLE request; and ANALYZE TABLE user;. Sometimes the optimizer is basing its choice on stale table statistics. It's harmless to run these statements against InnoDB tables. Oct 11 at 15:37
  • Both are ok with analyze Oct 11 at 16:01
  • 1
    Is LEFT important? If you remove it, the Optimizer may have more ways to perform the query.
    – Rick James
    Oct 11 at 18:40
0

Most JOIN queries are performed via "Nested Loop Join" (NLJ). This is where it walks through the rows of one table, and for each such row, it reaches into the next table to find the row(s) that match.

Usually this can be done via just indexes. However, your EXPLAIN shows that it can improve on that with Using join buffer (Block Nested Loop). This is where it loads the entire second table into a hash in memory. This is usually (not always) faster, sometimes much faster.

As already mentioned, the number of rows is so small that you can't necessarily extrapolate to larger tables.

I see no WHERE clause. Do you really want all rows? When there are a million requests, that will be an awful lot of rows to shovel across the network to the client.

If you will have a WHERE/GROUP/ORDER/LIMIT, then that could radically change the Explain plan.

As for (r.created_by > 0 AND r.created_by = u.id) -- It sounds like r.created_by = u.id belongs in the ON and r.created_by > 0 belongs in the WHERE. At that point, it may look for an index with created_by as the first column if it is a useful filter.

It may also transfer the > 0 to u.id > 0 because of the other relation.

Also, the EXPLAIN may have realized that all the created_ids were > 0, so it threw that out.

Or something else. Please focus on exactly the query that will be run in production; there are too many variations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.