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.

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these types together (for instance, a user may have a password and security token).

This query works fine and gives us exactly what we need. HOWEVER, it is constantly showing in the slow query log (possibly due to lack of indexes?). When I ask MySQL to "explain" the query it gives me:

You are correct that the last entry in the EXPLAIN output indicates the reason why it's showing up in the slow query log. You probably have log-queries-not-using-indexes enabled (which is a good thing).

Your query doesn't limit the credential_pairs table, so it has to compare each row to see if it has a match. You could try something like this, but I suspect it will have a similar EXPLAIN output.

MAJOR CHANGE #2 : Create an index to support subqueries

This should speed things up quite a bit, regardless what the EXPLAIN says about it.

Give it a Try !!!

UPDATE 2011-11-07 13:52 EST

In case you are wondering why you should refactor the query, I learned from a YouTube Video how to refactor queries. I added the idea of indexing to make sure subqueries in a refactored query ran fast. Of course, all subqueries make temp tables, which are united via Catesian Product. Yet, those subqueries are small. If you do not refactor the query, the tables are joined into a larger temp table first, and then the WHERE is evaluated.

I would put my money on a Cartesian Product of small temp tables over performing WHERE over a larger temp table anyday.

Just because it is in the slow query log does not necessarily mean that the query is slow. It might be in the slow query log as MySQL might categorize the query as not using indexes. It looks like you have sufficient indexes.

The questions is:
What is the query response time and is it acceptable to you or not?

EDIT
There might be a temporary table that gets moved from memory to disk for this query. Look at status variables for temporary tables created on disk and see if you would benefit from making the size limit higher for in memory temporary tables.