Skip to content

Instantly share code, notes, and snippets.

Avatar
🏠
Working from home

Simon Renoult simonrenoult

🏠
Working from home
View GitHub Profile
View explain_sql_no_cache_select.sql
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)
View optimize_table.sql
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)
View select_sql_no_cache.sql
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)
View analyze_table.sql
mysql> ANALYZE TABLE articles;
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| debug_sql.articles | analyze | status | OK |
+--------------------+---------+----------+----------+
1 row in set (0.02 sec)
View sql_no_cache.sql
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)
View show_index.sql
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)
View explain_select.sql
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)
View slow_query_log_1M.sql
# 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;
View article_schema.sql
CREATE TABLE articles (
article_id VARCHAR(32),
article_title VARCHAR(255),
article_content TEXT,
PRIMARY KEY (article_id)
);
View show_global_variables.sql
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');