A
A
Andrey Ivchenkov2017-03-14 13:25:28
MySQL
Andrey Ivchenkov, 2017-03-14 13:25:28

Why is sometimes a simple MySQL query running slowly?

There is a table (about 40 million rows):

CREATE TABLE `related_articles` (
  `articleID` int(32) DEFAULT NULL,
  `relatedArticleID` int(32) DEFAULT NULL,
  KEY `IX_ArticleID` (`articleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And there is a request:
SELECT relatedArticleID FROM related_articles WHERE articleID = 252381;

explain:
+----+-------------+------------------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | related_articles | ref  | IX_ArticleID  | IX_ArticleID | 5       | const |   10 | Using where |
+----+-------------+------------------+------+---------------+--------------+---------+-------+------+-------------+

Usually such requests are processed instantly. But sometimes in the logs they slip over time in 1-2 seconds.
With what it can be connected?
UPDATE: New data is periodically inserted into the table. Maybe it has something to do with it? A similar problem is observed with other tables.
UPDATE: SHOW INDEX FROM related_articles:
+------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| related_articles |          1 | IX_ArticleID |            1 | articleID   | A         |    10252773 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
k0nsu1, 2017-03-14
@k0nsu1

You need to make an index on the articleID field

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question