How to fix MySQL performance on JOIN queries with sorting

Max Kostikov

kostikov@zotum.net

IT voodoo

Местоположение:: Lisbon, Portugal

How to fix MySQL performance on JOIN queries with sorting

Fri, 01 Nov 2019 23:37:06 +0200

Max Kostikov

kostikov@zotum.net

I spent some time trying to fix query performance with MySQL when building categories list in widget include/contact_widgets.php. I found that its optimizer do not respects table mentions order in JOIN queries when performs ORDER BY so in some cases it dramaticaly increases query run time. But there is a way to fix it using special MySQL specific STRAIGHT_JOIN command.
Here is the hack how it can be fixed in Hubzilla code:
https://framagit.org/kostikov/core/commit/c71818096187dc84e6b44cf43bd178354b4c9de5
Probably this can be useful in other places if someone is having similar problems.

!Zotlabs|Hubzilla Development

show all 10 comments

Sun, 03 Nov 2019 16:47:38 +0200

Max Kostikov

kostikov@zotum.net

Certainly this is MySQL 8.0.(17?) issue.
Here is EXPLAIN from current query from MySQL 5.7

Нажмите, чтобы открыть/закрыть

root@localhost [hubzilla]> EXPLAIN SELECT DISTINCT term.term FROM term JOIN item ON term.oid = item.id WHERE item.uid = 42 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\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: 3232
     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_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
          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)

The same from MySQL 8.0

Нажмите, чтобы открыть/закрыть

root@localhost [hubzilla]> EXPLAIN SELECT DISTINCT term.term FROM term JOIN item ON term.oid = item.id 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\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: 570
     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.60
        Extra: Using where
2 rows in set, 1 warning (0.02 sec)

And MySQL 8.0 with STRAIGHT_JOIN which looks the same as 5.7 with JOIN

Нажмите, чтобы открыть/закрыть

root@localhost [hubzilla]> EXPLAIN SELECT DISTINCT term.term FROM term STRAIGHT_JOIN item ON term.oid = item.id 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\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: 5957
     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.01 sec)

Sun, 03 Nov 2019 21:22:17 +0200

Mario Vavti

mario@hub.somaton.com

Hve you tried to rebuild the index stats with optimize table? This might be important if you use InnoDB and had some changes to the table...

Sun, 03 Nov 2019 21:35:49 +0200

Max Kostikov

kostikov@zotum.net

@Mario Vavti Yes, this is InnoDB and sure, I did this while investigated.