| Bug #97462 | Slow JOIN with ORDER BY due wrong index selection | ||
|---|---|---|---|
| Submitted: | 3 Nov 2019 15:05 | Modified: | 12 Nov 2019 16:37 |
| Reporter: | Max Kostikov | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 8.0.17 | OS: | FreeBSD (12.0) |
| Assigned to: | CPU Architecture: | x86 | |
[3 Nov 2019 15:05]
Max Kostikov
[5 Nov 2019 12:57]
MySQL Verification Team
Hi Mr. Kostikov, Thank you for your bug report. However, we need more , much more info. First of all, why do you believe that 5.7 plan is better than the one in 8.0 ???? Second, what is a difference in the performance between JOIN and STRAIGHT_JOIN in 8.0. Do note, that in the moment of measurement, that query should be the only statement running on the server. After we get these answers and we would like to repeat the behaviour, you would need to send us all the data for the queries.
[5 Nov 2019 13:16]
Max Kostikov
First of all I suspect that this issue appeared in 8.0.17 only (but I can not to check at this moment to confirm). 5.7.27 was taken in comparison because of its availability and use about the same database. The difference between JOIN (I also tested LEFT JOIN but it performs with the same speed) and STRAIGHT_JOIN is 4-5 times (~2.3 - 2.5 sec vs ~0.5 - 0.6 sec). I have tested this on warmed servers without other load so numbers should be near to the reality. As you can EXPLAIN shows different query plan for JOIN and STRAIGHT_JOIN in 8.0.17 but it the same in 5.7.27.
[5 Nov 2019 13:26]
MySQL Verification Team
Hi Mr. Kostikov, Thank you for your answers. In order to verify this report, we have to be able to repeat it. Hence, please send us the dumps of all tables involved in those queries. You can use "Files" tab for that purpose.
[5 Nov 2019 13:54]
Max Kostikov
There is 2 tables. I can send you the download link in direct message (by email for example) because of data contains. My contact email is max@kostikov.co Thanks for understanding.
[5 Nov 2019 16:45]
MySQL Verification Team
Hi, Please, let us not exchange info via e-mail. If you look at the "Files" tab, you will see how to use our SFTP site. All files uploaded there are visible only to us, MySQL@Oracle employees.
[5 Nov 2019 17:05]
Max Kostikov
Oh, I got it now. Please see dbdump_191105.sql.gz file in /incoming
[8 Nov 2019 12:25]
MySQL Verification Team
Hi Mr. Kostikov, Thank you for your dump file .... However, dump file is huge. There are several of us that have to run the test case in the report processing .... Can you repeat the same problem with tables of the smaller size ??? You can filter out unnecessary data ....... Many thanks in advance .....
[8 Nov 2019 13:06]
Max Kostikov
I have not other sites or databases to test this. If it will help I can to filter out some unimportant fields in bigger table.
[8 Nov 2019 13:39]
MySQL Verification Team
Hi Mr. Kostikov, Thank you so much. That will indeed help. Use that same SFTP site and let us know when you are done. We are truly greteful.
[9 Nov 2019 11:00]
Max Kostikov
I have uploaded the new dump dbdump_191109.sql.gz Please use modified query for tests. SELECT DISTINCT term.term FROM term JOIN item ON term.oid = newitem.id WHERE newitem.uid = 42 AND term.uid = newitem.uid AND term.ttype = 3 AND term.otype = 1 AND newitem.owner_xchan = 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ' AND newitem.item_wall = 1 AND newitem.verb != 'http://activitystrea.ms/schema/1.0/update'; AND newitem.item_hidden = 0 AND newitem.item_type = 0 AND newitem.item_deleted = 0 AND newitem.item_unpublished = 0 AND newitem.item_delayed = 0 AND newitem.item_pending_remove = 0 AND newitem.item_blocked = 0 AND newitem.obj_type != 'http://purl.org/zot/activity/file'; AND item_private = 0 ORDER BY term.term ASC;
[12 Nov 2019 13:10]
MySQL Verification Team
Hi Mr. Kostikov, This is what I get with 8.0.18. This looks equivalent to me as 5.7. Do you agree ????
[12 Nov 2019 13:12]
MySQL Verification Team
Sorry, I forgot the output:
mysql> EXPLAIN SELECT DISTINCT term.term FROM term JOIN newitem ON term.oid = newitem.id
-> WHERE newitem.uid = 42
-> AND term.uid = newitem.uid
-> AND term.ttype = 3
-> AND term.otype = 1
-> AND newitem.owner_xchan =
-> 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ'
-> AND newitem.item_wall = 1
-> AND newitem.verb != 'http://activitystrea.ms/schema/1.0/update';
-> AND newitem.item_hidden = 0 AND newitem.item_type = 0 AND
-> newitem.item_deleted = 0
-> AND newitem.item_unpublished = 0 AND newitem.item_delayed = 0 AND
-> newitem.item_pending_remove = 0
-> AND newitem.item_blocked = 0 AND newitem.obj_type !=
-> 'http://purl.org/zot/activity/file';
-> AND item_private = 0
-> ORDER BY term.term ASC;
+----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+
| 1 | SIMPLE | term | NULL | index_merge | oid,otype,ttype,term,uid | uid,ttype,otype | 4,1,1 | NULL | 3797 | 100.00 | Using intersect(uid,ttype,otype); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | newitem | NULL | eq_ref | PRIMARY,uid_commented,uid_created,uid_item_unseen,uid_item_type,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,owner_xchan,verb,obj_type,item_wall,item_deleted_pending_remove_changed,item_pending_remove_changed,uid_mid | PRIMARY | 4 | test.term.oid | 1 | 5.00 | Using where; Distinct |
+----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.09 sec)
[12 Nov 2019 14:20]
Max Kostikov
Thanks for your responce. I can not test this with 8.0.18 but it seems I missed to add 3 indexes for item table in last dump. Can you please to add them for item_hidden, item_unpublished and item_delayed columns and repeat test in MySQL 8?
[12 Nov 2019 16:20]
MySQL Verification Team
Hi, I wasted time in adding the indices. The execution speed and the utput of the EXPLAIN is still the same : EXPLAIN SELECT DISTINCT term.term FROM term JOIN newitem ON term.oid = newitem.id WHERE newitem.uid = 42 AND term.uid = newitem.uid AND term.ttype = 3 AND term.otype = 1 AND newitem.owner_xchan = 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ' AND newitem.item_wall = 1 AND newitem.verb != 'http://activitystrea.ms/schema/1.0/update'; AND newitem.item_hidden = 0 AND newitem.item_type = 0 AND newitem.item_deleted = 0 AND newitem.item_unpublished = 0 AND newitem.item_delayed = 0 AND newitem.item_pending_remove = 0 AND newitem.item_blocked = 0 AND newitem.obj_type != 'http://purl.org/zot/activity/file'; AND item_private = 0 ORDER BY term.term ASC; +----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+ | 1 | SIMPLE | term | NULL | index_merge | oid,otype,ttype,term,uid | uid,ttype,otype | 4,1,1 | NULL | 3797 | 100.00 | Using intersect(uid,ttype,otype); Using where; Using temporary; Using filesort | | 1 | SIMPLE | newitem | NULL | eq_ref | PRIMARY,uid_commented,uid_created,uid_item_unseen,uid_item_type,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,owner_xchan,verb,obj_type,item_wall,item_deleted_pending_remove_changed,item_pending_remove_changed,uid_mid,item_hidden,item_unpublished,item_delayed | PRIMARY | 4 | test.term.oid | 1 | 5.00 | Using where; Distinct | +----+-------------+---------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+---------------+------+----------+--------------------------------------------------------------------------------+ 2 rows in set, 1 warning (2.71 sec) Can't repeat.
[12 Nov 2019 16:37]
Max Kostikov
Very strange. Perhaps the database size is matters because last time I gave other one. Also maybe this is 8.0.17 only issue.
I just rechecked this and can confirm difference between query execution plans for JOIN and STRAIGHT_JOIN.
# mysql -u root -p hubzilla
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 842171
Server version: 8.0.17 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [hubzilla]> EXPLAIN SELECT DISTINCT term.term FROM term JOIN item ON term.oid = item.id
AND term.ttype = 3
AND term.otype = 1
AND item.owner_xchan = 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ'
-> WHERE item.uid = 153
-> AND item.item_wall = 1
AND term.uid = item.uid
AND item.verb != 'http://activitystrea.ms/schema/1.0/update';
-> AND term.ttype = 3
AND item.item_hidden = 0 AND item.item_type = 0 AND item.item_deleted = 0
AND item.item_unpublished = 0 AND item.item_delayed = 0 AND item.item_pending_remove = 0
AND item.item_blocked = 0 AND item.obj_type != 'http://purl.org/zot/activity/file';
-> AND term.otype = 1
AND item_private = 0
-> ORDER BY term.term ASC;AND item.owner_xchan = 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ'
-> AND item.item_wall = 1
-> AND item.verb != 'http://activitystrea.ms/schema/1.0/update';
-> AND item.item_hidden = 0 AND item.item_type = 0 AND item.item_deleted = 0
-> AND item.item_unpublished = 0 AND item.item_delayed = 0 AND item.item_pending_remove = 0
-> AND item.item_blocked = 0 AND item.obj_type != 'http://purl.org/zot/activity/file';
-> AND item_private = 0
-> ORDER BY term.term ASC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: item
partitions: NULL
type: index_merge
possible_keys: PRIMARY,uid_commented,uid_created,uid_item_unseen,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,owner_xchan,verb,obj_type,item_wall,item_deleted_pending_remove_changed,item_pending_remove_changed,uid_item_type,uid_mid,item_hidden,item_unpublished,item_delayed
key: uid_item_wall,owner_xchan,uid_item_private,uid_item_type,item_hidden,item_unpublished,item_delayed
key_len: 5,764,5,8,1,1,1
ref: NULL
rows: 502
filtered: 1.29
Extra: Using intersect(uid_item_wall,owner_xchan,uid_item_private,uid_item_type,item_hidden,item_unpublished,item_delayed); Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: term
partitions: NULL
type: ref
possible_keys: oid,otype,ttype,term,uid
key: oid
key_len: 4
ref: hubzilla.item.id
rows: 3
filtered: 1.55
Extra: Using where
2 rows in set, 1 warning (0.02 sec)
root@localhost [hubzilla]> EXPLAIN SELECT DISTINCT term.term FROM term STRAIGHT_JOIN item ON term.oid = item.id
ORDER BY term.term ASC; -> WHERE item.uid = 153
-> AND term.uid = item.uid
-> AND term.ttype = 3
-> AND term.otype = 1
-> AND item.owner_xchan = 'Whu-c1UTzykRhJSX5z-kdmoVk1E4N1hVnZwebTJbiteGU3TI0SXKXZPib7kiT0S4Ftlq3-ja74bVIEzD_qFIcQ'
-> AND item.item_wall = 1
-> AND item.verb != 'http://activitystrea.ms/schema/1.0/update';
-> AND item.item_hidden = 0 AND item.item_type = 0 AND item.item_deleted = 0
-> AND item.item_unpublished = 0 AND item.item_delayed = 0 AND item.item_pending_remove = 0
-> AND item.item_blocked = 0 AND item.obj_type != 'http://purl.org/zot/activity/file';
-> AND item_private = 0
-> ORDER BY term.term ASC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: term
partitions: NULL
type: index_merge
possible_keys: oid,otype,ttype,term,uid
key: uid,ttype,otype
key_len: 4,1,1
ref: NULL
rows: 7100
filtered: 100.00
Extra: Using intersect(uid,ttype,otype); Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: item
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,uid_commented,uid_created,uid_item_unseen,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,owner_xchan,verb,obj_type,item_wall,item_deleted_pending_remove_changed,item_pending_remove_changed,uid_item_type,uid_mid,item_hidden,item_unpublished,item_delayed
key: PRIMARY
key_len: 4
ref: hubzilla.term.oid
rows: 1
filtered: 5.00
Extra: Using where; Distinct
2 rows in set, 1 warning (0.00 sec)
