MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize subquery, replace IN() with JOIN
Posted by: Miron jajtic
Date: November 16, 2016 03:22PM

Hello Peter,

Excellent, thank you very much.
Your query is working on production database in less than 0.3 sec.


EXPLAIN with old IN() query:
+------+--------------+-----------------------+-------+--------------------------------------+---------------+---------+---------------------------------------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-----------------------+-------+--------------------------------------+---------------+---------+---------------------------------------------------+-------+------------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 799 | Using temporary; Using filesort |
| 1 | PRIMARY | sadrzaj | range | PRIMARY,objavljen,tip | tip | 152 | NULL | 17352 | Using index condition; Using where |
| 2 | MATERIALIZED | kategorija_sadrzaj_se | ref | kategorija_id,sadrzaj_id | sadrzaj_id | 4 | const | 1 | |
| 2 | MATERIALIZED | kategorija_sadrzaj_se | ref | kategorija_id,sadrzaj_id,sadrzaj_tip | kategorija_id | 4 | slobodna_main.kategorija_sadrzaj_se.kategorija_id | 799 | Using where |
+------+--------------+-----------------------+-------+--------------------------------------+---------------+---------+---------------------------------------------------+-------+------------------------------------+
4 rows in set (0.00 sec)



EXPLAIN with new JOIN query:
+------+-------------+-------+--------+--------------------------------------+---------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+--------------------------------------+---------------+---------+-------------------------------+------+---------------------------------+
| 1 | SIMPLE | b | ref | kategorija_id,sadrzaj_id | sadrzaj_id | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | kategorija_id,sadrzaj_id,sadrzaj_tip | kategorija_id | 4 | slobodna_main.b.kategorija_id | 799 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,objavljen,tip | PRIMARY | 4 | slobodna_main.a.sadrzaj_id | 1 | Using where |
+------+-------------+-------+--------+--------------------------------------+---------------+---------+-------------------------------+------+---------------------------------+
3 rows in set (0.00 sec)


Requested info:

~~~~~~~~~~~~~~~~~~~~~~~~
innodb_buffer_pool_size=5120M
~~~~~~~~~~~~~~~~~~~~~~~~
total used free shared buff/cache available
Mem: 125G 8.6G 1.7G 2.4G 115G 114G
Swap: 4.0G 5.5M 4.0G
Total: 129G 8.6G 5.7G
~~~~~~~~~~~~~~~~~~~~~~~~
Server version: 5.6.33-cll-lve MySQL Community Server (GPL)
~~~~~~~~~~~~~~~~~~~~~~~~

Now I see that version is "cll-lve" (Cloudlinux with MySQL Governor) and user resource usage was restricted almost all the time because of slow query.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[Wed Nov 16 16:10:19 2016] slobodna LIMIT_ENFORCED period short av.value, field cpu value 396/limit 380 loadavg(5.23 4.03 3.17 5/726 611644) vmstat( 4 0 5632 9933748 4971364 111278656 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.956745, read = 0, write = 1083
[Wed Nov 16 16:10:20 2016] slobodna LIMIT_ENFORCED period short av.value, field cpu value 396/limit 380 loadavg(5.13 4.03 3.17 5/730 611658) vmstat( 4 0 5632 9923480 4971364 111278328 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.955563, read = 0, write = 812
[Wed Nov 16 16:10:21 2016] slobodna LIMIT_ENFORCED period short av.value, field cpu value 396/limit 380 loadavg(5.13 4.03 3.17 8/734 611698) vmstat( 5 0 5632 9931020 4971364 111275968 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.954147, read = 0, write = 812
[Wed Nov 16 16:10:22 2016] slobodna LIMIT_ENFORCED period short av.value, field cpu value 389/limit 380 loadavg(5.13 4.03 3.17 4/734 611775) vmstat( 3 0 5632 10002128 4971364 111247792 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.886342, read = 0, write = 4327
[Wed Nov 16 16:10:23 2016] slobodna LIMIT_ENFORCED period short av.value, field cpu value 382/limit 380 loadavg(5.13 4.03 3.17 4/732 611791) vmstat( 3 0 5632 10024248 4971364 111230544 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.814457, read = 0, write = 3514
[Wed Nov 16 16:10:24 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 390/limit 350 loadavg(5.13 4.03 3.17 4/731 611797) vmstat( 3 0 5632 10072868 4971368 111187624 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.893232, read = 0, write = 1960
[Wed Nov 16 16:10:25 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 388/limit 350 loadavg(4.96 4.01 3.17 5/731 611802) vmstat( 4 0 5632 10108988 4971368 111147048 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.877241, read = 0, write = 1893
[Wed Nov 16 16:10:26 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 386/limit 350 loadavg(4.96 4.01 3.17 3/729 611818) vmstat( 2 0 5632 10118852 4971368 111147056 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.857031, read = 0, write = 1825
[Wed Nov 16 16:10:27 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 383/limit 350 loadavg(4.96 4.01 3.17 5/728 611830) vmstat( 5 0 5632 10099880 4971368 111147064 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.824642, read = 0, write = 1825
[Wed Nov 16 16:10:28 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 380/limit 350 loadavg(4.96 4.01 3.17 3/722 611840) vmstat( 2 0 5632 10114720 4971368 111147040 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.792383, read = 0, write = 1825
[Wed Nov 16 16:10:29 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 376/limit 350 loadavg(4.96 4.01 3.17 3/722 611850) vmstat( 2 0 5632 10114580 4971368 111147360 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.759473, read = 0, write = 1825
[Wed Nov 16 16:10:30 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 373/limit 350 loadavg(4.73 3.98 3.17 3/722 611863) vmstat( 2 0 5632 10106696 4971368 111147904 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.726380, read = 0, write = 1825
[Wed Nov 16 16:10:31 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 368/limit 350 loadavg(4.73 3.98 3.17 2/722 611877) vmstat( 1 0 5632 10113504 4971368 111147872 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.677915, read = 0, write = 1825
[Wed Nov 16 16:10:32 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 363/limit 350 loadavg(4.73 3.98 3.17 3/721 611893) vmstat( 2 0 5632 10144636 4971372 111114256 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.628840, read = 0, write = 1825
[Wed Nov 16 16:10:33 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 358/limit 350 loadavg(4.73 3.98 3.17 4/719 611909) vmstat( 3 0 5632 10048776 4971372 111117912 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.579719, read = 0, write = 1825
[Wed Nov 16 16:10:34 2016] slobodna LIMIT_ENFORCED period middle av.value, field cpu value 354/limit 350 loadavg(4.73 3.98 3.17 3/715 611918) vmstat( 2 0 5632 10114400 4971372 111068504 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.530441, read = 0, write = 1825
[Wed Nov 16 16:10:35 2016] slobodna LIMIT_ENFORCED period long av.value, field cpu value 316/limit 300 loadavg(4.35 3.91 3.15 2/712 611928) vmstat( 1 0 5632 10212632 4971376 111068528 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.156326, read = 0, write = 1218
[Wed Nov 16 16:10:36 2016] slobodna LIMIT_ENFORCED period long av.value, field cpu value 316/limit 300 loadavg(4.35 3.91 3.15 2/710 611947) vmstat( 1 0 5632 10189128 4971376 111089832 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.153076, read = 0, write = 1218
[Wed Nov 16 16:10:37 2016] slobodna LIMIT_ENFORCED period long av.value, field cpu value 315/limit 300 loadavg(4.35 3.91 3.15 1/708 611964) vmstat( 0 0 5632 10172436 4971376 111108808 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.149758, read = 0, write = 1218
[Wed Nov 16 16:10:38 2016] slobodna LIMIT_ENFORCED period long av.value, field cpu value 315/limit 300 loadavg(4.35 3.91 3.15 1/708 611986) vmstat( 0 0 5632 10173132 4971380 111108608 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.146441, read = 0, write = 1218
[Wed Nov 16 16:10:39 2016] slobodna LIMIT_ENFORCED period long av.value, field cpu value 315/limit 300 loadavg(4.35 3.91 3.15 2/706 611995) vmstat( 1 0 5632 10196636 4971384 111082800 1 1 434 390 20 23 21 9 70 0 0) cpu = 3.143164, read = 0, write = 1218
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize subquery, replace IN() with JOIN
978
November 16, 2016 03:22PM


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.