Slow queries cost money and cause incidents. Identifying them from the shell — without a GUI — requires parsing slow query logs, extracting execution plans, and correlating query patterns with time-of-day performance data.
1
MySQL slow query log analysis
BASH
# ── Enable slow query logging ─────────────────────────────
mysql --defaults-file="${DB_CONF}" -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'"
# ── Parse slow query log with mysqldumpslow ────────────────
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log | head -60
# ── Parse slow log manually with AWK ──────────────────────
awk '
/^# Time:/{ts=$3" "$4}
/^# Query_time:/{qt=$3; rows=$7}
/^(SELECT|INSERT|UPDATE|DELETE)/{
if (qt+0 > 2.0)
printf "%.2fs rows=%-6s %s\n", qt, rows, substr($0,1,80)
}' /var/log/mysql/slow.log | sort -rn | head -20
# ── Top slow queries by frequency ─────────────────────────
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# ── Real-time slow query monitoring ───────────────────────
mysql --defaults-file="${DB_CONF}" -BNs -e "
SELECT id, user, time, state, LEFT(info,100) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep' AND time > 10
ORDER BY time DESC" | awk -F$'\t' '{printf " [%4ds] user=%-10s state=%-15s %s\n",$3,$2,$4,$5}
2
EXPLAIN plan extraction and index recommendations
BASH
# ── Get EXPLAIN for a query ───────────────────────────────
db() { mysql --defaults-file="${DB_CONF}" myapp -e "$1"; }
db "EXPLAIN SELECT u.name, COUNT(o.id) AS orders
FROM users u LEFT JOIN orders o ON o.user_id=u.id
WHERE u.active=1
GROUP BY u.id
ORDER BY orders DESC
LIMIT 10"
# ── Check for full table scans (no index) ─────────────────
db "EXPLAIN SELECT * FROM orders WHERE status='pending'" | awk 'NR>1 && $5=="ALL" {print "WARN: Full table scan on", $1, $2}'
# ── Find tables without primary keys ─────────────────────
db "SELECT TABLE_NAME FROM information_schema.TABLES t
WHERE TABLE_SCHEMA='myapp'
AND TABLE_TYPE='BASE TABLE'
AND NOT EXISTS (
SELECT 1 FROM information_schema.TABLE_CONSTRAINTS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA
AND c.TABLE_NAME=t.TABLE_NAME
AND c.CONSTRAINT_TYPE='PRIMARY KEY'
)"
# ── Index usage statistics ────────────────────────────────
db "SELECT OBJECT_NAME AS tbl,
INDEX_NAME AS idx,
COUNT_STAR AS uses,
COUNT_READ AS reads,
COUNT_WRITE AS writes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA='myapp'
AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_STAR DESC LIMIT 20"✔ ✔ Performance analys — Enable slow query logging in production with
long_query_time=1 to catch any query over 1 second. Use mysqldumpslow -s t -t 20 to find the 20 slowest queries by cumulative time. Always EXPLAIN queries that appear in slow logs. Check performance_schema.table_io_waits_summary_by_index_usage for unused indexes — they consume write overhead for no read benefit.