There’s a table:
`id` INT(10) NOT NULL AUTO_INCREMENT,
`status` TINYINT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
When I processing the flowing statement (There’s more than 1000 records in this table), a table scan is processed. Why primary key index does not work?
SELECT * FROM t_test WHERE id = FLOOR(1 + RAND() * 1000)
And sometimes there 2 different results, why would this happen?
index invalid when mysql select by random id and sometimes return tow different values
>Solution :
When doing this:
WITH abc AS (SELECT FLOOR(1 + RAND() * 1000) as R)
SELECT * FROM t_test WHERE id = (SELECT R FROM abc);
You might expect that the CTE only returns one record, and the index can be used.
but when adding EXPLAIN it shows UNCACHEABLE:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | t_test | null | ALL | null | null | null | null | 1024 | 100.00 | Using where |
| 2 | UNCACHEABLE | SUBQUERY | null | system | null | null | null | null | 1 | 100.00 | |
| 3 | DERIVED | null | null | null | null | null | null | null | null | null | No tables used |