Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

I'm not really sure why you expected log_queries_not_using_indexes=1 to not log queries that didn't use an index. The idea of the setting is to see which queries are getting run on your server that could be ideal candidates for optimization. For your example:

SELECT id_test from test where id_test != 69

Unless you change the requirements of the query, there's not much you can do. Of course it's faster to do a full scan and return all rows that don't match that id_test value.

As for ideas on how to approach analysis:

  • You can set min_examined_row_limit to a higher value. If, for example, you want to exclude queries that examine less than 5,000 rows, set this variable to 5000. This is useful to reduce the 'noise' in the slow query log.

  • You can use a tool called pt-query-digest with the --review option to review your slow queries. The tool allows you to store your review history so you don't repeat work on the same query during analysis:

    It won’t show you queries you’ve already reviewed. A query is considered to be already reviewed if you’ve set a value for the reviewed_by column. (If you want to see queries you’ve already reviewed, use the --report-all option.)

    It also has the added benefit of storing a history of the queries that get logged in your slow query. Who wouldn't love historical evidence of improving your queries?

share|improve this answer
    
Thank you for your time. I basically wanted to only have queries listed for tables where I actually might consider adding an index. MySQL's behavior is rather counterintuitive in this respect as it even would log stuff where indexes cannot be added any more (because they are there already). But I guess I could use min_examined_row_limit as a workaround. –  syneticon-dj Apr 19 '13 at 1:12
1  
Sorry I couldn't be more help. I'm not sure how MySQL would intuitively know indexes couldn't be added anymore. Many of the times if the optimizer deems it better to not use any index, that doesn't mean the query can't be tuned. But sometimes the query can't be tuned. That's where the history aspect of pt-query-digest comes in handy, to skip those queries from further review. –  Derek Downey Apr 19 '13 at 1:17
    
After pondering around with pt-query-digest I found it extremely useful for exactly the reasons you've cited. Thanks for that. –  syneticon-dj Apr 24 '13 at 7:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.