MySQL Forums
Forum List  »  Performance

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>

Options: ReplyQuote


Subject
Views
Written By
Posted
1997
April 21, 2015 11:15AM
846
April 22, 2015 07:17AM
781
April 22, 2015 07:05PM
988
April 23, 2015 11:42PM
783
April 23, 2015 11:55PM
764
April 24, 2015 05:22PM
763
April 25, 2015 02:31AM
848
April 25, 2015 02:41AM
Re: Slow query
1471
April 25, 2015 05:22AM
817
April 25, 2015 10:42PM
794
May 13, 2015 03:30AM
872
April 28, 2015 03:40AM


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.