Mysql slow query log indicates few rows in table as low performance

Question

explain extended  SELECT check_fine from local_profiles WHERE (name = 'myprofile');

+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+ 
|  1 | SIMPLE      | local_profiles  | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------+-------------+

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(256) | NO   |     | NULL    |                |
| check_fine   | tinyint(4)   | NO   |     | 0       |                |
+--------------+--------------+------+-----+---------+----------------+

mysql slow query log setting (log-queries-not-using-indexes = 0)

# Query 10: 0.03 QPS, 0.19x concurrency, ID xxxxxxxxxxxxxxxxx at byte 53863711
# This item is included in the report because it matches --outliers.
# Scores: V/M = 1.17


# Rank Query ID           Response time     Calls R/Call  V/M   Item
# ==== ================== ================= ===== ======= ===== ==========
#    5 xxxxxxxxxxxxxxxxx  16710.3680  1.8%  2203  7.5853  1.17 SELECT local_profiles


# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    2468
# Exec time      1  18379s      5s     33s      7s     13s      3s      6s
# Lock time      0   471ms    64us    32ms   258us   259us   896us    90us
# Rows sent      0   1.16k       0       1    0.54    0.99    0.49    0.99
# Rows examine   0   1.16k       0       1    0.54    0.99    0.49    0.99
# Query size     0 121.71k      57      57      57      57       0      57

I have a table which has only five rows with three columns. However above mentioned simple query is shows in the mysql slow query log.

How this simple query will be impact on performance.

what does mean by This item is included in the report because it matches --outliers

enter code here

Looks to me like the table is 0 or 1 row long, not 5? How the heck can it take 5-33 _seconds_ to scan such a tiny table? Is this query part of a larger transaction? — Rick James, Mar 20 '16 at 17:22

score 0 · Answer 1 · answered Mar 17 '16 at 17:34

I can answer one of the two questions...

2. what does mean by This item is included in the report because it matches --outliers

That looks like the output of pt-query-digest which has a --outliers option. It's used to control which queries are reported as slow. It takes arguments, so why this query was chosen depends on what was passed into it.

Mysql slow query log indicates few rows in table as low performance

1 Answers1