MySQL Bugs: #73960: SQL performance problem

Bug #73960	SQL performance problem
Submitted:	18 Sep 2014 3:31	Modified:	19 Oct 2014 13:15
Reporter:	Anuo Anuo	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
this query is very slow , there is not in other db such as sqlserver.

select a row in 80 ten thousand records:

select a from table where a=( FLOOR(0 + (RAND() * 912993)))

How to repeat:
select a row in 80 ten thousand records:

select a from table where a=( FLOOR(0 + (RAND() * 912993)))

Thank you for the bug report. Please when reporting a bug follow the instructions how to report, provide a complete test case: the exact server version 5.6.XX, the create table statement and insert data dump file. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I've made a testcase on 8.0.14 on windows,  it looks like this:

---
select version();
drop table if exists t;
create table t(a int not null auto_increment primary key)engine=innodb;
insert into t values (),(),(),(),(),(),(),(),(),(),();
insert into t(a) select null from t a,t b,t c,t d,t e,t f;
analyze table t;
explain select a from t where a=( floor(0 + (rand() * 77777)));
flush status;
select a from t where a=( floor(0 + (rand() * 77777)));
show status like '%handler%';

-- vs what was meant:

set @a:=floor(rand()*77777);
explain select a from t where a=@a;
flush status;
select a from t where a=@a;
show status like '%handler%';
---

Output:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t(a int not null auto_increment primary key)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t values (),(),(),(),(),(),(),(),(),(),();
Query OK, 11 rows affected (0.02 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> insert into t(a) select null from t a,t b,t c,t d,t e,t f;
Query OK, 1771561 rows affected (11.72 sec)
Records: 1771561  Duplicates: 0  Warnings: 0

mysql> analyze table t;
+--------+---------+----------+----------+
| Table  | Op      | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status   | OK       |
+--------+---------+----------+----------+
1 row in set (0.10 sec)

mysql> explain select a from t where a=( floor(0 + (rand() * 77777)));
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1769850 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t where a=( floor(0 + (rand() * 77777)));
+-------+
| a     |
+-------+
| 47188 |
+-------+
1 row in set (0.41 sec)

mysql> show status like '%handler%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 1771572 | <-------- index scan...
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
18 rows in set (0.00 sec)

mysql> set @a:=floor(rand()*77777);
Query OK, 0 rows affected (0.00 sec)

mysql> explain select a from t where a=@a;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select a from t where a=@a;
+-------+
| a     |
+-------+
| 76157 |
+-------+
1 row in set (0.00 sec)

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

mysql>