Skip to content

Instantly share code, notes, and snippets.

@paneq

paneq/explain.txt

Created September 3, 2015 10:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save paneq/d3251a13319e9486dcc2 to your computer and use it in GitHub Desktop.
Save paneq/d3251a13319e9486dcc2 to your computer and use it in GitHub Desktop.
Download ZIP
mysql using index based on attribute type
Raw
explain.txt
mysql> EXPLAIN SELECT `my_table_name_xxxxxxxxxxxxx`.* FROM `my_table_name_xxxxxxxxxxxxx` WHERE `my_table_name_xxxxxxxxxxxxx`.`order_id` = 2015522 LIMIT 1;
+----+-------------+-----------------------------+------+-----------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+------+-----------------------------------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | my_table_name_xxxxxxxxxxxxx | ALL | index_my_table_name_xxxxxxxxxxxxx_on_order_id | NULL | NULL | NULL | 280511 | Using where |
+----+-------------+-----------------------------+------+-----------------------------------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT `my_table_name_xxxxxxxxxxxxx`.* FROM `my_table_name_xxxxxxxxxxxxx` WHERE `my_table_name_xxxxxxxxxxxxx`.`order_id` = '2015522' LIMIT 1;
+----+-------------+-----------------------------+------+-----------------------------------------------+-----------------------------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+------+-----------------------------------------------+-----------------------------------------------+---------+-------+------+-------------+
| 1 | SIMPLE | my_table_name_xxxxxxxxxxxxx | ref | index_my_table_name_xxxxxxxxxxxxx_on_order_id | index_my_table_name_xxxxxxxxxxxxx_on_order_id | 768 | const | 1 | Using where |
+----+-------------+-----------------------------+------+-----------------------------------------------+-----------------------------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment