This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000; | |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | |
| 1 | SIMPLE | articles | NULL | ALL | PRIMARY | NULL | NULL | NULL | 996520 | 10.00 | Using where | | |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | |
1 row in set, 4 warnings (0.00 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> OPTIMIZE TABLE articles; | |
+--------------------+----------+----------+-------------------------------------------------------------------+ | |
| Table | Op | Msg_type | Msg_text | | |
+--------------------+----------+----------+-------------------------------------------------------------------+ | |
| debug_sql.articles | optimize | note | Table does not support optimize, doing recreate + analyze instead | | |
| debug_sql.articles | optimize | status | OK | | |
+--------------------+----------+----------+-------------------------------------------------------------------+ | |
2 rows in set (10.46 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000; | |
+------------+---------------+-----------------+ | |
| article_id | article_title | article_content | | |
+------------+---------------+-----------------+ | |
| 150000 | Title 150000 | Content 150000 | | |
+------------+---------------+-----------------+ | |
1 row in set, 1 warning (0.65 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> ANALYZE TABLE articles; | |
+--------------------+---------+----------+----------+ | |
| Table | Op | Msg_type | Msg_text | | |
+--------------------+---------+----------+----------+ | |
| debug_sql.articles | analyze | status | OK | | |
+--------------------+---------+----------+----------+ | |
1 row in set (0.02 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SELECT SQL_NO_CACHE * FROM articles WHERE article_id = 150000; | |
+------------+---------------+-----------------+ | |
| article_id | article_title | article_content | | |
+------------+---------------+-----------------+ | |
| 150000 | Title 150000 | Content 150000 | | |
+------------+---------------+-----------------+ | |
1 row in set, 1 warning (0.80 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SHOW INDEX FROM articles; | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | |
| articles | 0 | PRIMARY | 1 | article_id | A | 1158879 | NULL | NULL | | BTREE | | | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | |
1 row in set (0.00 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> EXPLAIN SELECT * FROM articles WHERE article_id = 150000; | |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | |
| 1 | SIMPLE | articles | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1271825 | 10.00 | Using where | | |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | |
1 row in set, 3 warnings (0.01 sec) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Time: 2019-06-26T14:33:07.346902Z | |
# Query_time: 0.658942 Lock_time: 0.000174 Rows_sent: 0 Rows_examined: 999986 | |
SET timestamp=1561559587; | |
select * from articles where article_id = 150000; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE articles ( | |
article_id VARCHAR(32), | |
article_title VARCHAR(255), | |
article_content TEXT, | |
PRIMARY KEY (article_id) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SHOW GLOBAL VARIABLES WHERE Variable_name IN ('long_query_time', 'slow_query_log', 'slow_query_log_file', 'log_queries_not_using_indexes', 'log_output'); |