Re: Slow query
Posted by:
Manu Swami
Date: April 25, 2015 05:22AM
The explain plan for the problematic query is enforcing block_nested_loop=on as a part of default optimization technique. I turned it off for the open session and ran the explain plan and query again. It started using the index on imsiid and It took less than a second to fetch 500 records.
I then turned it off globally by defining the variable optimizer_switch='block_nested_loop=off' in my.cnf file and ran the export from GUI. It took less than 2 minutes to export 2.6 million rows.
mysql> EXPLAIN SELECT dn.id DN,
-> dn.pt,
-> dnASD.asdId,
-> dn_imsi.imsiId,
-> IF (dn_imsi.imsiId IS NOT NULL, CONCAT_WS(':', imsi_ne.neType, HEX(imsi_ne.neId)),
-> GROUP_CONCAT(CONCAT_WS(':', dnNE.neType, HEX(dnNE.neId)))) neList,
-> dnNS.nsType,
-> dnNS.nsdn,
-> dnBL.cgbl,
dnBL.cgbl,
-> dnBL.cdbl
-> FROM dn
-> LEFT JOIN dn_dn as dnNS ON id = dnNS.dnId
-> LEFT JOIN dn_bl as dnBL ON id = dnBL.dnId
-> LEFT JOIN dn_asd as dnASD ON id = dnASD.dnId
-> LEFT JOIN dn_ne AS dnNE ON id = dnNE.dnId
-> LEFT JOIN dn_imsi ON dn.id = dn_imsi.dnId
-> LEFT JOIN imsi_ne ON dn_imsi.imsiId = imsi_ne.imsiId
-> WHERE id > '\0\0\0\0hI' AND dn_imsi.imsiId IS NULL
-> GROUP BY dn.id
-> ORDER BY dn.id
-> LIMIT 500;
+----+-------------+---------+--------+---------------+---------+---------+-----
| id | select_type | table | type | possible_keys | key | key_len | ref
+----+-------------+---------+--------+---------------+---------+---------+-----
| 1 | SIMPLE | dn | range | PRIMARY | PRIMARY | 9 | NULL
| 1 | SIMPLE | dnNS | eq_ref | PRIMARY | PRIMARY | 9 | pdb.
| 1 | SIMPLE | dnBL | eq_ref | PRIMARY,dnId | PRIMARY | 9 | pdb.
| 1 | SIMPLE | dnASD | eq_ref | PRIMARY | PRIMARY | 9 | pdb.
| 1 | SIMPLE | dnNE | ref | dnId | dnId | 9 | pdb.
| 1 | SIMPLE | dn_imsi | eq_ref | PRIMARY | PRIMARY | 9 | pdb.
| 1 | SIMPLE | imsi_ne | ALL | imsiId | NULL | NULL | NULL
+----+-------------+---------+--------+---------------+---------+---------+-----
7 rows in set (0.02 sec)
mysql> SELECT dn.id DN, -> dn.pt,
-> dnASD.asdId,
-> dn_imsi.imsiId,
-> IF (dn_imsi.imsiId IS NOT NULL, CONCAT_WS(':', imsi_ne.neType, HEX(imsi_ne.neId)),
-> GROUP_CONCAT(CONCAT_WS(':', dnNE.neType, HEX(dnNE.neId)))) neList,
-> dnNS.nsType,
-> dnNS.nsdn,
-> dnBL.cgbl,
-> dnBL.cdbl
-> FROM dn
-> LEFT JOIN dn_dn as dnNS ON id = dnNS.dnId
-> LEFT JOIN dn_bl as dnBL ON id = dnBL.dnId
-> LEFT JOIN dn_asd as dnASD ON id = dnASD.dnId
-> LEFT JOIN dn_ne AS dnNE ON id = dnNE.dnId
-> LEFT JOIN dn_imsi ON dn.id = dn_imsi.dnId
-> LEFT JOIN imsi_ne ON dn_imsi.imsiId = imsi_ne.imsiId
-> WHERE id > '\0\0\0\0hI' AND dn_imsi.imsiId IS NULL
-> GROUP BY dn.id
-> ORDER BY dn.id
-> LIMIT 2;
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
| DN | pt | asdId | imsiId | neList | nsType | nsdn | cgbl | cdbl |
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
| | -1 | NULL | NULL | | NULL | NULL | NULL | NULL |
| | 25 | | NULL | 1:02000000000000012F | 1 | | 1 | 1 |
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
2 rows in set (27.21 sec)
mysql> SET block_nested_loop=off;
ERROR 1193 (HY000): Unknown system variable 'block_nested_loop'
mysql> set optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)
mysql> set SESSION optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT dn.id DN, dn.pt, dnASD.asdId, dn_imsi.imsiId, IF (dn_imsi.imsiId IS NOT NULL, CONCAT_WS(':', imsi_ne.neType, HEX(imsi_ne.neId)), GROUP_CONCAT(CONCAT_WS(':', dnNE.neType, HEX(dnNE.neId)))) neList, dnNS.nsType, dnNS.nsdn, dnBL.cgbl, dnBL.cdbl FROM dn LEFT JOIN dn_dn as dnNS ON id = dnNS.dnId LEFT JOIN dn_bl as dnBL ON id = dnBL.dnId LEFT JOIN dn_asd as dnASD ON id = dnASD.dnId LEFT JOIN dn_ne AS dnNE ON id = dnNE.dnId LEFT JOIN dn_imsi ON dn.id = dn_imsi.dnId LEFT JOIN imsi_ne ON dn_imsi.imsiId = imsi_ne.imsiId WHERE id > '\0\0\0\0hI' AND dn_imsi.imsiId IS NULL GROUP BY dn.id ORDER BY dn.id LIMIT 2;
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
| DN | pt | asdId | imsiId | neList | nsType | nsdn | cgbl | cdbl |
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
| | -1 | NULL | NULL | | NULL | NULL | NULL | NULL |
| | 25 | | NULL | 1:02000000000000012F | 1 | | 1 | 1 |
+-----------+------+--------+--------+----------------------+--------+-----------+------+------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>