Re: Optimize subquery, replace IN() with JOIN
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~