Help optimizing query in the MySQL slow-query log

Question

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).

In an attempt to see if a pair match, there is the following query:

SELECT DISTINCT cp.credential_id FROM credential_pairs AS cp
INNER JOIN credentials AS c1 ON (cp.primary_credential_id = c1.credential_id)
INNER JOIN credentials AS c2 ON (cp.secondary_credential_id = c2.credential_id)
WHERE c1.data = AES_ENCRYPT('Some Value 1', 'encryption key')
AND c2.data = AES_ENCRYPT('Some Value 2', 'encryption key');

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:

+----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+-------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+-------+-------+--------------------------------+
| 1 | SIMPLE | c1 | ref | credential_id_UNIQUE,credential_id,ix_credentials_data | ix_credentials_data | 22 | const | 1 | Using where; Using temporary |
| 1 | SIMPLE | c2 | ref | credential_id_UNIQUE,credential_id,ix_credentials_data | ix_credentials_data | 22 | const | 1 | Using where |
| 1 | SIMPLE | cp | ALL | NULL | NULL | NULL | NULL | 69197 | Using where; Using join buffer |
+----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+-------+-------+--------------------------------+

I have a feeling that last entry (where it shows 69197 rows) is probably the problem, but I am FAR from a DBA... help?

---

credentials table:

CREATE TABLE `credentials` (
 `hidden_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `credential_id` varchar(255) NOT NULL,
 `data` blob NOT NULL,
 `credential_status` varchar(100) NOT NULL,
 `insert_date` datetime NOT NULL,
 `insert_user` int(10) unsigned NOT NULL,
 `update_date` datetime DEFAULT NULL,
 `update_user` int(10) unsigned DEFAULT NULL,
 `delete_date` datetime DEFAULT NULL,
 `delete_user` int(10) unsigned DEFAULT NULL,
 `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`hidden_id`,`credential_id`),
 UNIQUE KEY `credential_id_UNIQUE` (`credential_id`),
 KEY `credential_id` (`credential_id`),
 KEY `data` (`data`(10)),
 KEY `credential_status` (`credential_status`(10))
) ENGINE=InnoDB AUTO_INCREMENT=1572 DEFAULT CHARSET=utf8;

credential_pairs Table:

CREATE TABLE `credential_pairs` (
 `hidden_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `credential_id` varchar(255) NOT NULL,
 `primary_credential_id` varchar(255) NOT NULL,
 `secondary_credential_id` varchar(255) NOT NULL,
 `is_deleted` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`hidden_id`,`credential_id`),
 KEY `primary_credential_id` (`primary_credential_id`(10)),
 KEY `secondary_credential_id` (`secondary_credential_id`(10))
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=latin1;

credentials Indexes:

+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| credentials | 0 | PRIMARY | 1 | hidden_id | A | 186235 | NULL | NULL | | BTREE | |
| credentials | 0 | PRIMARY | 2 | credential_id | A | 186235 | NULL | NULL | | BTREE | |
| credentials | 0 | credential_id_UNIQUE | 1 | credential_id | A | 186235 | NULL | NULL | | BTREE | |
| credentials | 1 | credential_id | 1 | credential_id | A | 186235 | NULL | NULL | | BTREE | |
| credentials | 1 | ix_credentials_data | 1 | data | A | 186235 | 20 | NULL | | BTREE | |
+-------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

credential_pair Indexes:

+------------------+------------+---------------------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+---------------------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| credential_pairs | 0 | PRIMARY | 1 | hidden_id | A | 69224 | NULL | NULL | | BTREE | |
| credential_pairs | 0 | PRIMARY | 2 | credential_id | A | 69224 | NULL | NULL | | BTREE | |
| credential_pairs | 1 | ix_credential_pairs_credential_id | 1 | credential_id | A | 69224 | 36 | NULL | | BTREE | |
| credential_pairs | 1 | ix_credential_pairs_primary_credential_id | 1 | primary_credential_id | A | 69224 | 36 | NULL | | BTREE | |
| credential_pairs | 1 | ix_credential_pairs_secondary_credential_id | 1 | secondary_credential_id | A | 69224 | 36 | NULL | | BTREE | |
+------------------+------------+---------------------------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+

• hafichuk: can you show the indexes that you have for each table? SHOW INDEXES FROM credential and SHOW INDEXES FROM credential_pairs
• Jeremy Logan: @halfichuk Added.

Answer 1

I think one issue is the shortened lengths of these two indexes in the credential_pairs table:

KEY `primary_credential_id` (`primary_credential_id`(10)),
KEY `secondary_credential_id` (`secondary_credential_id`(10))

---

Another thing is the datatype of the data column. Can you change it from blob to VARCHAR?

Answer 2

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.

SELECT DISTINCT cp.credential_id FROM credential_pairs AS cp
WHERE EXISTS (SELECT * FROM credentials WHERE primary_credential_id = credential_id 
 AND data=AES_ENCRYPT('Some Value 1', 'encryption key'))
 AND EXISTS(SELECT * FROM credentials WHERE secondary_credential_id = credential_id 
 AND data=AES_ENCRYPT('Some Value 2', 'encryption key')) 

• Jeremy Logan: Yeah... the WHERE output is basically identical :(
• Derek Downey: @fixedd what is the value of your join_buffer_size and tmp_table_size ?

Answer 3

You need to do two major things:

MAJOR CHANGE #1 : Refactor query to perform WHERE clauses first, and JOINs last

SELECT DISTINCT cp.credential_id
FROM credential_pairs AS cp
INNER JOIN (SELECT credential_id,data FROM credentials
WHERE data=AES_ENCRYPT('Some Value 1','encryption key')) c1
ON cp.primary_credential_id = c1.credential_id
INNER JOIN (SELECT credential_id,data FROM credentials
WHERE data=AES_ENCRYPT('Some Value 2','encryption key')) c2
ON cp.secondary_credential_id = c2.credential_id;

MAJOR CHANGE #2 : Create an index to support subqueries

ALTER TABLE credentials ADD INDEX data_credential_id_ndx (data,credential_id);

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.

I actually tried this technique out in StackOverflow and beat out 10 other answers. Please read it carefully. I also advise you watch the YouTube Video thoroughly. It changed the way I look at queries..

• KM.: Mostly curious, but can you explain why "WHEREs" should precede "JOINs"?
• RolandoMySQLDBA: @KM01 : I added the answer to your question in my answer.
• Jeremy Logan: I tried your query and your index and the query response time is basically exactly the same as before (and explain makes it look like it's still doing a table scan).
• srini.venigalla: @Rolando, your fix is not applicable here. Here the problem is obfuscation the column data.
• srini.venigalla: @Rolando, I doubt either of your suggestions would help.

Answer 4

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.

• Jeremy Logan: It's taking 1/3 of a second to churn through those 70k rows... that seems too slow to me.

Answer 5

can you use this query instead?

SELECT DISTINCT cp.credential_id FROM credential_pairs AS cp
INNER JOIN credentials AS c1 ON (cp.primary_credential_id = c1.credential_id)
WHERE c1.data in (
AES_ENCRYPT('Some Value 1', 'encryption key'), 
AES_ENCRYPT('Some Value 2', 'encryption key'));

• ypercubeᵀᴹ: This not an equivalent query.