Description:
Consider a table of 1 mln records or so.
mysql> desc http_auth;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(64) | | PRI | | |
| data | int(11) | | | 0 | |
+-------+-------------+------+-----+---------+-------+
id = is an md5(1..1000000); data - random int(11).
select is working as expected with the following condition in where:
mysql> select * from http_auth where id=md5(125032);
+----------------------------------+-----------+
| id | data |
+----------------------------------+-----------+
| 3c9030a3b66a42015b2e33c05384beff | 162152954 |
+----------------------------------+-----------+
1 row in set (0.00 sec)
mysql> explain select * from http_auth where id=md5(1);
+----+-------------+-----------+-------+---------------+---------+---------+----
---+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+----
---+------+-------+
| 1 | SIMPLE | http_auth | const | PRIMARY | PRIMARY | 64 | con
st | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+----
---+------+-------+
But changing the condition in where to the following makes the query behave in an unexpected way:
mysql> select * from http_auth where id=md5(round(rand()*1000000));
+----------------------------------+-----------+
| id | data |
+----------------------------------+-----------+
| 9fff279c396ab84db6f49570998d45d0 | 411695429 |
+----------------------------------+-----------+
1 row in set (9.67 sec)
mysql> explain select * from http_auth where id=md5(round(rand()*1000000));
+----+-------------+-----------+------+---------------+------+---------+------+-
--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-
--------+-------------+
| 1 | SIMPLE | http_auth | ALL | NULL | NULL | NULL | NULL |
1001000 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-
--------+-------------+
1 row in set (0.00 sec)
I was also able to reproduce a select with not only a single record returned:
mysql> select * from http_auth where id=md5(round(rand()*1000000));
+----------------------------------+------+
| id | data |
+----------------------------------+------+
| e3557d8696f24b9d9314a96c38fa5a8e | 1 |
| a935a0899fec674b6271930965fbaaa0 | 0 |
+----------------------------------+------+
2 rows in set (6.08 sec)
Isn't it that md5(round(rand()*1000000)) is a constant?
Same behaviour on 4.0.21.
How to repeat:
Create table and populate it with random data:
CREATE TABLE `http_auth` (
`id` char(64) NOT NULL default '',
`data` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
);
More in "description".
