As I was analyzing slow queries, I came across one executed by the Droptor module. The following is generated by mk-query-digest:

# Query 11: 0.00 QPS, 0.00x concurrency, ID 0x8677D342B7ECB8BF at byte 18080427
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0]*, V/M = 0.95
# Query_time sparkline: |      ^ |
# Time range: 2011-04-27 09:05:29 to 23:05:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      30
# Exec time      1      1s       0      1s    33ms       0   178ms       0
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0     105       0       7    3.50    6.98    3.49    6.98
# Rows examine   0 218.65k   4.79k   9.79k   7.29k   9.33k   2.31k   9.33k
# Query size     0   4.25k     145     145     145     145       0     145
# String:
# Databases    driverpack... (15/50%), niab_callc... (15/50%)
# Hosts        localhost
# Users        driverpack... (15/50%), niab_callc... (15/50%)
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `niab_callcenter` LIKE 'watchdog'\G
#    SHOW CREATE TABLE `niab_callcenter`.`watchdog`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT message, variables, timestamp FROM watchdog WHERE message LIKE "login attempt failed%" AND now()-timestamp > 60*60 ORDER BY timestamp DESC\G

You may want to look into improving that query. I tried adding an index on (source, language), but according to the EXPLAIN output, that's not sufficient. I guess that due to the ORDER BY, no index is being used? (I'm no expert in query optimization.)

Comments

jemond’s picture

Assigned: Unassigned » jemond

Hi Wim,

Thanks for your note and excellent analysis of this.

I will have to see if there are other ways I an tease this data out. Let me look and I will update you.

jpe