# aes\_decrypt performance

**URL:** <https://forums.percona.com/t/aes-decrypt-performance/1032>\
**Category:** Other MySQL® Questions\
**Created:** [December 11, 2008, 2:34pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032 "2008-12-11T14:34:04Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mylesmg](https://avatars.discourse-cdn.com/v4/letter/m/e9c0ed/32.png) [@mylesmg](https://forums.percona.com/u/mylesmg)\
**Post date:** [December 11, 2008, 2:34pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032/1 "2008-12-11T14:34:04Z")

</div>

I currently am having major performance issues with a query that searches through an encrypted table for unencrypted values. Each row has its own salt on the encryption. This is a deduplicating query to find duplicate records and then perform an action on the duplicates. Here is the query that is causing the problem:

SELECT pri\_key\_fld FROM tbl WHERE AES\_DECRYPT(`field_one`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordone’ AND AES\_DECRYPT(`field_two`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordtwo’ AND AES\_DECRYPT(`field_three`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordthree’ AND pri\_key\_fld \<\> 123456789 LIMIT 1;

The table has about 500,000 records in it, and in reality there are about 100 blob fields holding encypted data. My solution must remain encrypted because that is what my clients purchased, so I can’t change it now without customer dissatisfaction. Here is the table structure:

CREATE TABLE tbl (  
pri\_key\_fld int(11) NOT NULL,  
field\_one blob NOT NULL,  
field\_two blob NOT NULL,  
field\_three blob NOT NULL,  
PRIMARY KEY (pri\_key\_fld)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It has challenged me, and I hope someone out there can help me with this. Thanks in advance.

---

<div class="post-metadata">

**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\
**Post date:** [December 11, 2008, 5:10pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032/2 "2008-12-11T17:10:42Z")

</div>

The problem is that this query needs to check every row:

Put

| [B]Quote:[/B] |
| 

explain SELECT pri\_key\_fld FROM tbl WHERE AES\_DECRYPT(`field_one`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordone’ AND AES\_DECRYPT(`field_two`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordtwo’ AND AES\_DECRYPT(`field_three`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordthree’ AND pri\_key\_fld \<\> 123456789 LIMIT 1;

 |

 inside SQL tab in phpmyadmin and see used key column and rows column. First one will be NULL and second will contain number of records in the table.

Maybe You could crypt with AES wordone, wordtwo and wordthree (or one of them) and change query into: `field_one` = ‘encrypted\_wordone’ … ? Those blobs have got limited length ? I`m not sure if there is possibility to set index or fulltext on to the blob column, but You could try. One index should discard some of the rows, maybe all of unnecessary ?

edit:  
You could write mysql function which will:

- AES\_ENCRYPT wordone, wordtwo and wordthree  
( something like @wordone\_enc := select AES\_ENCRYPT( ‘salt’, wordone ); )
- use those encrypted words in select query

---

<div class="post-metadata">

**Author:** ![mylesmg](https://avatars.discourse-cdn.com/v4/letter/m/e9c0ed/32.png) [@mylesmg](https://forums.percona.com/u/mylesmg)\
**Post date:** [December 11, 2008, 9:23pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032/3 "2008-12-11T21:23:30Z")

</div>

Ok, that was a great idea. I tried a few options and it appears that no index at all is the fastest. I tried with prefix lengths of 4, 8, and 16 and measured the results. The longer the index became, the faster it went. The fastest though, by far, was NO index at all.

The interesting thing is that it was marginally faster to encrypt the needle instead of decrypting the field.

So we now have achieved a small optimization of about 3/100 of a second per query, which is not really significant. It IS an optimization though, and it may manifest itself as much more under load.

I still think that there may be a better way than the following (which is currently the best option I have tried):

SELECT pri\_key\_fld FROM tbl WHERE AES\_ENCRYPT(‘wordone’, CONCAT(‘salt’,pri\_key\_fld)) = `field_one` AND AES\_ENCRYPT(‘wordtwo’, CONCAT(‘salt’,pri\_key\_fld)) = `field_two` AND AES\_ENCRYPT(‘wordthree’, CONCAT(‘salt’,pri\_key\_fld)) = `field_three` AND pri\_key\_fld \<\> 123456789 LIMIT 1;

is better than the current production query of

explain SELECT pri\_key\_fld FROM tbl WHERE AES\_DECRYPT(`field_one`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordone’ AND AES\_DECRYPT(`field_two`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordtwo’ AND AES\_DECRYPT(`field_three`, CONCAT(‘salt’,pri\_key\_fld)) = ‘wordthree’ AND pri\_key\_fld \<\> 123456789 LIMIT 1;

I still think that there is just something I am missing.

---

<div class="post-metadata">

**Author:** ![bluesaga](https://avatars.discourse-cdn.com/v4/letter/b/f19dbf/32.png) [@bluesaga](https://forums.percona.com/u/bluesaga)\
**Post date:** [January 4, 2009, 2:55pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032/4 "2009-01-04T14:55:08Z")

</div>

Your issue is that mysql needs to decrypt the data each time, and it is in a blob format so you have:

A) No useful index (you can’t index the decrypted data)  
B) No Fulltext search

Leaving you with a full table scan on every query.

It would probably be better in your case to select all three encrypted values and then use these values in your select query as januzi recommended:

@wordone\_enc := select AES\_ENCRYPT( ‘salt’, ‘wordone’ );@wordtwo\_enc := select AES\_ENCRYPT( ‘salt’, ‘wordtwo’ );@wordthree\_enc := select AES\_ENCRYPT( ‘salt’, ‘wordthree’ ); SELECT pri\_key\_fld FROM tbl WHERE `field_one` = @wordone\_enc AND `field_two` = @wordtwo\_enc AND`field_three` = @wordthree\_enc AND pri\_key\_fld \<\> 123456789 LIMIT 1;

An index on “field\_one, field\_two, field\_three, pri\_key\_fld” may help, but I am unsure how mysql handles blobs (maybe change to fulltext if the words are not too big?)

Note: I have not checked the syntax etc of the above query

---

<div class="post-metadata">

**Author:** ![mylesmg](https://avatars.discourse-cdn.com/v4/letter/m/e9c0ed/32.png) [@mylesmg](https://forums.percona.com/u/mylesmg)\
**Post date:** [January 5, 2009, 3:09pm UTC](https://forums.percona.com/t/aes-decrypt-performance/1032/5 "2009-01-05T15:09:10Z")

</div>

Thanks for the reply. That’s a great idea, and I hadn’t thought of it prior. How can this work when the salt is actually a keyphrase plus the primary key:

@salt := CONCAT(‘abcdefg’,pri\_key)