I am trying to use log_queries_not_using_indexes = 1
to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, queries are logged whenever the optimizer really decided not to use an index as a criterion in a WHERE clause. And not if they truly have no indexes matching the filtered columns.
So given a table with the following structure
CREATE TABLE `test` (
`id_test` int(11) NOT NULL AUTO_INCREMENT,
`some_text` varchar(255) DEFAULT NULL,
`some_more_text` text,
PRIMARY KEY (`id_test`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
a query SELECT id_test from test where id_test != 69
would be logged to the slow log because of not using indexes (the optimizer has decided that a table scan is more efficient as not much could be won by using an index) but SELECT id_test from test where id_test = 69
would not.
I would expect the behavior of the latter query in the first case as well since the index is present. As is, it makes troubleshooting missing indexes rather tiresome. Ideas on how to approach this greatly appreciated.