How to find un-indexed queries in MySQL, using tcpdump | Hacker News Reader

How to find un-indexed queries in MySQL, using tcpdump

xaprb.com

38 pointsakirk10 comments

This IS a very neat trick-now does anyone know how to find un-indexed queries in PostgreSQL?

Switch to MySQL, and then use this trick. (I kid, I kid.)

Although this is a stunning trick which I will probably use myself, I think finding un-indexed queries is the wrong way of going about things. Sometimes queries are slow even if they do use indexes, so the best route is to find slow queries.

In MySQL finding slow queries is a pain because you STILL have to restart to enable the slow query log. In Postgres you don't (just set log_min_duration_statement to 0 and reload). Then you can use pgFouine (http://pgfouine.projects.postgresql.org) to analyze them - it produces a nice report.

Un-indexed queries are not inherently evil... sometimes they are faster than using/maintaining an index.

Queries are all about context; how big of a data set, how much/fast is that data set changing, how often is the query being made, etc.

I've seen some cases where a single table had a crap-load of indexes, one specifically for each query that was being performed. The overhead of maintaining all of those indexes was killing performance.

I find the most effective query tuning is a result of having realistic stress/load testing combined with internal analytics from the database (EXPLAIN or EXPLAIN PLAN). This points out the low hanging fruit, which you can optimize using various methods such as indexes, materialized views, and in some cases, re-arranging the data model to better suit your queries.

It also gives you a better idea of which queries are being hit the most, and which ones it makes sense to spend your time tuning.

I don't think you have to restart to enable the query log, you should be able to just /etc/init.d/mysql reload

Use log_duration in postgresql.conf to log long queries, explain ANALYZE them to find out what they're doing. Sometimes a full table sequential scan is faster than using an index -- e.g. if the index would (according to gathered statistics data) hit 90% of the table anyway.

There are also various other gathered statistics you can look at, e.g. cache hits, index hits etc.