slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 5
log-queries-not-using-indexes
To enable this debugging, we need to modify the MySQL configuration file (normally my.cnf). Below are settings working for MySQL version 5.6.21 on Debian 7.7 environment.
# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld .
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..
# tail /var/log/mysql/localhost-slow.log
# User@Host: snort[snort] @ localhost [] Id: 154
# Query_time: 0.000824 Lock_time: 0.000022 Rows_sent: 1 Rows_examined: 1815
SET timestamp=1421036399;
SELECT count(*) FROM acid_event;
# User@Host: snort[snort] @ localhost [] Id: 154
# Query_time: 0.001872 Lock_time: 0.000029 Rows_sent: 1 Rows_examined: 1815
SET timestamp=1421036399;
SELECT COUNT(DISTINCT acid_event.ip_src), COUNT(DISTINCT acid_event.ip_dst) FROM acid_event;
# User@Host: snort[snort] @ localhost [] Id: 154
# Query_time: 0.001689 Lock_time: 0.000031 Rows_sent: 1 Rows_examined: 1815
SET timestamp=1421036399;
SELECT COUNT(DISTINCT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto) FROM acid_event;
# User@Host: snort[snort] @ localhost [] Id: 154
# Query_time: 0.002063 Lock_time: 0.000031 Rows_sent: 1 Rows_examined: 1815
SET timestamp=1421036399;
SELECT COUNT(DISTINCT layer4_sport), COUNT(DISTINCT layer4_dport) FROM acid_event;
# User@Host: snort[snort] @ localhost [] Id: 154
# Query_time: 0.001989 Lock_time: 0.000039 Rows_sent: 1 Rows_examined: 1815
SET timestamp=1421036399;
SELECT COUNT(DISTINCT acid_event.layer4_sport), COUNT(DISTINCT acid_event.layer4_dport) FROM acid_event WHERE ip_proto='6';