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.