MySQL – logging SQL statements

Using recently MySQL (5.5.23) in a project to persist JBoss JMS messages (among other things), I wanted to see if any slow or un-indexed queries were issued, and apparently the database server allows to log those through a simple change in my.ini configuration file.

• log-slow-queries[=file_name]
  If not specifying a file name, it will create a HOSTNAME-slow.log; in my case (Windows 2008 R2) the log was created in C:\ProgramData\MySQL\MySQL Server 5.5\data
• long_query_time
  Queries taking longer than specified amount of seconds will get logged.
• log-queries-not-using-indexes
  Logs queries not using indexes or performing a table / index scan.

my.ini
#
# Slow queries
#
log-slow-queries
long_query_time = 1
log-queries-not-using-indexes

I found a small un-indexed query on JMS_USERS - although a small table of only 5 records, it does get issued repeatedly so I’ve added an index for it.

# User@Host: jboss[jboss] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 5
SET timestamp=1334525528;
SELECT JMSUSERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID='ID:1';

Also found a SELECT COUNT(*) query that was basically scanning the table - I could remove this one by changing mysql-ds.xml from using a <check-valid-connection-sql> statement to using <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

# User@Host: jboss[jboss] @ localhost [127.0.0.1]
# Query_time: 0.000000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 5
SET timestamp=1334525528;
SELECT COUNT(JMSUSERID) FROM JMS_USERS;

If you want to log all queries there’s another parameter for that, placing all queries in HOSTNAME.log, very helpful in improve performance further by simply reducing the amount of operations.

my.ini
#
# All queries
#
general-log = 1