MySQL Forums
Forum List  »  InnoDB

Innodb: join of large table to small table is slow if small table has less than 7 rows
Posted by: Jeroen van den Hoed
Date: September 23, 2014 10:07AM

Hello all,

This is my first post to the Mysql forums. Thank you for reading.

I have a simple query joining a large table (>250K rows) to a small table (just 1 row) . All the records in the large table refer to this 1 record in the small table. My real world tables and query are more complicated, but this illustrates my issue well enough.

SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;

The query runs relatively slow (0.19s). Using EXPLAIN I know the tables are joined in an suboptimal order (Using temporary; Using filesort Using where; Using join buffer (Block Nested Loop)).

But when I add at least 6 other records to the small table (making a total of 7 rows), the query runs fast (0.00s) and uses the primary keys. Adding 5 record won't do it.

I'm running 5.6.19-0ubuntu0.14.04.1 on a x86_64 with persistent statistics turned ON. I've tried analyze/optimize table, and increasing the sample pages vars (innodb_stats_persistent_sample_pages, innodb_stats_sample_pages, innodb_stats_transient_sample_pages), but to no avail.

Can anybody explain why this happens? Is it a bug or should I do something different?

Thank you,
Jeroen


Below my queries to reproduce this issue.


#create the large table
CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, t2_id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#create the small table
CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#insert 1 record in the small table
INSERT INTO t2 (name) VALUES ('small table');

#insert 262144 records in the large table; we do this by inserting 1 record and exponentially duplicating the records 18 times
INSERT INTO t1 (name, t2_id) VALUES ('large table', 1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);
INSERT INTO t1 (name, t2_id) (SELECT name, t2_id FROM t1);

#we now have 262144 identical rows in t1


#run the join query; this runs relatively slow; 0.19s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
20 rows in set (0.19 sec)

#run explain; the primary keys are NOT used
EXPLAIN SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 262010 | Using temporary; Using filesort |
| 1 | SIMPLE | t2 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
2 rows in set (0.00 sec)


#add 6 more records to the small table
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');
INSERT INTO t2 (name) VALUES ('small table');

#run the query again; now it is fast; 0.00s
SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,10;
+----+-------------+----+-------------+
| id | name | id | name |
+----+-------------+----+-------------+
| 1 | large table | 1 | small table |
| 2 | large table | 1 | small table |
| 3 | large table | 1 | small table |
| 4 | large table | 1 | small table |
| 6 | large table | 1 | small table |
| 7 | large table | 1 | small table |
| 8 | large table | 1 | small table |
| 9 | large table | 1 | small table |
| 13 | large table | 1 | small table |
| 14 | large table | 1 | small table |
+----+-------------+----+-------------+
10 rows in set (0.00 sec)

#run explain; the primary keys are used
EXPLAIN SELECT t1.id, t1.name, t2.id, t2.name FROM t1, t2 WHERE t1.t2_id=t2.id ORDER BY t1.id LIMIT 0,20;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 20 | NULL |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.t2_id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Innodb: join of large table to small table is slow if small table has less than 7 rows
4522
September 23, 2014 10:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.