MySql Performance Question: MD5(value)

md5MySQL

for security purpose I do some queries in this way:

SELECT avatar_data FROM users WHERE MD5(ID) ='md5value'

So, for example I have this entries:

-TABLE.users-
ID | avatar_data
39 | some-data

I do this query:

SELECT avatar_data FROM users WHERE MD5(ID) ='d67d8ab4f4c10bf22aa353e27879133c'

'd67d8ab4f4c10bf22aa353e27879133c' is the '39' value filtered by MD5.

I have a VERY large database with a lot of entries. I wonder if this approach might compromise the DB performance?

Best Answer

Because you are using a function on the column you want to search ( MD5(ID)= ), MySQL will have to do a full table scan.

While I am not sure your reason for doing a search like that, to speed things up, I can suggest you add another column with the processed ID data and index it.

So you should do:

SELECT * FROM user WHERE MD5_ID = 'd67d8ab4f4c10bf22aa353e27879133c'

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternatively, try nesting the subquery deeper into a from clause ...

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.

... but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

You probably have to change it for both the client (you are running to do the import) AND the daemon mysqld that is running and accepting the import.

For the client, you can specify it on the command line:

mysql --max_allowed_packet=100M -u root -p database < dump.sql

Also, change the my.cnf or my.ini file (usually found in /etc/mysql/) under the mysqld section and set:

max_allowed_packet=100M

or you could run these commands in a MySQL console connected to that same server:

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

(Use a very large value for the packet size.)

Best Answer

Related Solutions

MySQL Error 1093 – Can’t specify target table for update in FROM clause

MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

Related Topic