Description:
A SELECT on PK field with a ROUND(RAND()) leads to a full table scan instead of a (PK) index access.
Further the result is empty.
How to repeat:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(64) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM
;
INSERT INTO test VALUES (NULL, 'blablablablablablablablablablablabla', NULL);
INSERT INTO test SELECT NULL, 'blablablablablablablablablablablabla', NULL FROM test;
...
explain
select * from resources
WHERE id = 512
AND status >= 0
;
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | resources | ref | id,status | id | 8 | const | 50 | Using where |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
But:
explain
select * from resources
WHERE id = ROUND(RAND()*4096, 0)
AND status >= 0
;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | resources | ALL | status | NULL | NULL | NULL | 131173 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
Suggested fix:
No idea.
