{"id":27,"date":"2013-05-07T15:27:34","date_gmt":"2013-05-07T15:27:34","guid":{"rendered":"https:\/\/code4reference.com\/?p=27"},"modified":"2013-05-07T15:27:34","modified_gmt":"2013-05-07T15:27:34","slug":"code4referencedebugging-sql-query","status":"publish","type":"post","link":"https:\/\/code4reference.com\/?p=27","title":{"rendered":"Code4ReferenceDebugging SQL query"},"content":{"rendered":"

Recently I started writing SQL query for analyzing and debugging the production code. But I was surprised to see that some queries takes longer time to execute to achieve the same output. I did research and found some interesting stuff about how to debug the SQL query. I have a very simple table who\u2019s definition is as following. In test environment, this table was populated with more then 1000K rows.<\/p>\n

\n+-----------------------+--------------+------+-----+----------------+\n| Field                 | Type         | Null | Key | Extra          |\n+-----------------------+--------------+------+-----+----------------+\n| id                    | bigint(20)   | NO   | PRI | auto_increment |\n| dateCreated           | datetime     | NO   |     |                |\n| dateModified          | datetime     | NO   |     |                |\n| phoneNumber           | varchar(255) | YES  | MUL |                |\n| version               | bigint(20)   | NO   |     |                |\n| oldPhoneNumber        | varchar(255) | YES  |     |                |\n+-----------------------+--------------+------+-----+----------------+\n<\/pre>\n
I executed a very simple query to find the tuple which contains 5107357058 as phoneNumber. It took almost 4 seconds to fetch the result.<\/p>\n
\nselect * from Device where phoneNumber = 5107357058;\ntakes 4 sec.\n<\/pre>\n
This simple query should have taken few milliseconds. I noticed that phoneNumber datatype is varchar but in query it is provided as number. When I modify the query to match the datatype, it took few milliseconds.<\/p>\n
\nselect * from Device where phoneNumber = '5107357058';\ntakes almost no time.\n<\/pre>\n
After googling and reading post on stackoverflow I found EXPLAIN<\/code> SQL clouse which helps in debugging the query. The EXPLAIN<\/code> statement provides information about the execution plan for a SELECT<\/code>statement. When I used it to get the information about the two queries I got the following results.<\/p>\n
\nmysql> EXPLAIN select * from Device where phoneNumber = 5107357058;\n+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+\n| id | select_type | table     | type | possible_keys                         | key  | key_len | ref  | rows    | Extra       |\n+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+\n|  1 | SIMPLE      | Device    | ALL  | phoneNumber,idx_Device_phoneNumber    | NULL | NULL    | NULL | 6482116 | Using where |\n+----+-------------+-----------+------+---------------------------------------+------+---------+------+---------+-------------+\n1 row in set (0.00 sec)\n\nmysql> EXPLAIN select * from Device where phoneNumber = '5107357058';\n+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+\n| id | select_type | table     | type | possible_keys                         | key         | key_len | ref   | rows | Extra       |\n+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+\n|  1 | SIMPLE      |   Device  | ref  | phoneNumber,idx_Device_phoneNumber    | phoneNumber | 258     | const |    2 | Using where |\n+----+-------------+-----------+------+---------------------------------------+-------------+---------+-------+------+-------------+\n1 row in set (0.00 sec)\n<\/pre>\n
The EXPLAIN<\/code>gives you different query attribute. While analysing the query you should take care of the following attribute.<\/p>\n
    \n
  • possible_keys :<\/code> shows the indexes apply to the query<\/li>\n
  • key :<\/code> which key use to find the record. NULL values shows that there was no key used for the query and SQL search linearly, eventually takes long time.<\/li>\n
  • rows :<\/code> SQL query with less number of result-rows, is efficient. One should always try to improve the query and avoid using generic query clouse. The query performance is much evident when executed on large number of records.<\/li>\n
  • type :<\/code> is \u201cThe join type\u201d. Ref<\/code> means All rows with matching index values are read from the table; All<\/code> means the full table scan.<\/li>\n<\/ul>\n
    The two outputs of EXPLAIN clearly indicate the subtle differences. The later query uses the string<\/code> which is right datatype, results phoneNumber as key and checks only two rows. Whereas the former uses integer in the query which is different datatype and hence SQL<\/code> converts to integer value to string value and compares with each record present in that table. This results NULL<\/code> as key and 6482116 as row output. You can also see that the later query type value is ref<\/code> and former query type value is All<\/code>, which clearly indicates that the former is a bad query.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Recently I started writing SQL query for analyzing and debugging the production code. But I was surprised to see that some queries takes longer time to execute to achieve the same output. I did research and found some interesting stuff about how to debug the SQL query. I have a very simple table who\u2019s definition […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-27","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts\/27","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=27"}],"version-history":[{"count":0,"href":"https:\/\/code4reference.com\/index.php?rest_route=\/wp\/v2\/posts\/27\/revisions"}],"wp:attachment":[{"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=27"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=27"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code4reference.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=27"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}