

How to find un-indexed queries in MySQL, using tcpdump - akirk
http://www.xaprb.com/blog/2009/08/18/how-to-find-un-indexed-queries-in-mysql-without-using-the-log/

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

~~~
russss
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.

~~~
nettdata
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.

------
thaumaturgy
Thank you for posting some actual content. This is a really neat trick.

------
joshu
wireshark does this as well, but this looks a bit more flexible.

------
trevelyan
easier: login to mysql and type "show processlist".

