simonrenoult

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)

simonrenoult

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)

simonrenoult

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)

simonrenoult

mysql> ANALYZE TABLE articles;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| debug_sql.articles | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.02 sec)

simonrenoult

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)

simonrenoult

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)

simonrenoult

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)

simonrenoult

# 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;

simonrenoult

CREATE TABLE articles (
    article_id VARCHAR(32),
    article_title VARCHAR(255),
    article_content TEXT,
    PRIMARY KEY (article_id)
);

simonrenoult

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');