aes_decrypt performance

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.

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

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.

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 ANDfield_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

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)