Query Performance Monitoring

Over in bug 481261 I am adding support in mozStorage to warn when a query doesn’t use an index to sort. This is a debug only warning that uses NS_WARNING to tell you what query is at fault, and the total number of sort operations that were used on it.

Luckily, SQLite exposes a handy function that tells us this information. In general, you want to use a index in your ORDER BY clause so SQLite can use it to generate the order the results a given back. If you do not use an index, SQLite has to first get all the results in memory, then it sorts them, and then it starts to return results. If you expect a lot of results, this can get very expensive.

There were suggestions on the newsgroups to also add an API so debuggers such as ChromeBug or SQLite Manager could listen for these types of errors. I’ll be filing follow-up bugs for some of these suggestions later on.