Active
Project:
Droptor
Version:
6.x-2.8
Component:
Code
Priority:
Minor
Category:
Task
Assigned:
Reporter:
Created:
27 Apr 2011 at 22:42 UTC
Updated:
27 Apr 2011 at 22:50 UTC
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
Comment #1
jemond commentedHi 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