Issue Bug Closed

#87723Missing Index on sys_log table

Christoph Lehmann 02/15/19, 12:08 updated 05/07/19, 12:10

Description

While analysing the slow.log with pt-query-digest i see

# Query 1: 0.17 QPS, 0.11x concurrency, ID 0xD52AD4861404CBA7 at byte 195329
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-02-15T10:34:39 to 2019-02-15T10:34:51
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0       2
# Exec time     64      1s   636ms   687ms   661ms   687ms    36ms   661ms
# Lock time      0   104us    46us    58us    52us    58us     8us    52us
# Rows sent      0       2       1       1       1       1       0       1
# Rows examine  92   2.69M   1.35M   1.35M   1.35M   1.35M       0   1.35M
# Query size     0     172      86      86      86      86       0      86
# String:
# Databases    dbname
# Hosts        localhost
# Users        dbname
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `dbname` LIKE 'sys_log'\G
#    SHOW CREATE TABLE `dbname`.`sys_log`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(`error`) FROM `sys_log` WHERE (`tstamp` >= 0) AND (`error` IN (-1, 1, 2))\G

The query is done for the error counter in the topbar in backend. 600ms for a query is fatal. It may be okay to have big tables leading to longer execution time, but queries should be optimized

I suggest using a further index on that table:

CREATE INDEX errorcount ON sys_log (tstamp,error)

!without-index.png!
!errorcounter.png!
with-index.png

Reviews 1