Description:
mysql> show create table ashe\G
*************************** 1. row ***************************
Table: ashe
Create Table: CREATE TABLE `ashe` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`name_1` varchar(100) NOT NULL DEFAULT 'cccccccccccccc',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=262121 DEFAULT CHARSET=utf8mb4 COMMENT='ccc'
1 row in set (0.00 sec)
mysql> select count(*),name from ashe group by name;
+----------+------+
| count(*) | name |
+----------+------+
| 147455 | aaa |
| 1 | bbb |
+----------+------+
2 rows in set (0.61 sec)
mysql> explain select * from ashe.ashe where name ='aaa';
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | ashe | NULL | ref | name | name | 43 | const | 73720 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysqld select index name.
ashe@macos ~ $ time mysql -S /tmp/mysql_13307.sock -uroot -A -D ashe -e "set profiling=1;select * from ashe where name='aaa';show profiles;show profile for query 1" > /data/1
real 0m3.954s
user 0m0.379s
sys 0m0.025s
ashe@macos ~ $less /data/1
Query_ID Duration Query
1 3.67799900 select * from ashe where name='aaa'
Status Duration
starting 0.000067
checking permissions 0.000010
Opening tables 0.000221
init 0.000046
System lock 0.000019
optimizing 0.000019
statistics 0.000244
preparing 0.000022
executing 0.000005
Sending data 3.671858
end 0.000015
query end 0.000080
closing tables 0.000022
freeing items 0.000073
logging slow query 0.005276
cleaning up 0.000022
but if we force index primary,
ashe@macos ~ $ time mysql -S /tmp/mysql_13307.sock -uroot -A -D ashe -e "set profiling=1;select * from ashe force index(primary) where name='aaa';show profiles;show profile for query 1" > /data/2
real 0m1.249s
user 0m0.383s
sys 0m0.020s
Query_ID Duration Query
1 0.98165800 select * from ashe force index(primary) where name='aaa'
Status Duration
starting 0.000091
checking permissions 0.000014
Opening tables 0.000154
init 0.000060
System lock 0.000024
optimizing 0.000023
statistics 0.000040
preparing 0.000029
executing 0.000006
Sending data 0.981117
end 0.000015
query end 0.000021
closing tables 0.000020
freeing items 0.000027
cleaning up 0.000017
How to repeat:
CREATE TABLE `ashe` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`name_1` varchar(100) NOT NULL DEFAULT 'cccccccccccccc',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB
for(int I=0;i<=147455;i++){
insert into ashe(name) values(aaa);
}
update ashe set name='bbb' where id=1;
and run my test as Description.
Suggested fix:
I will give some information after view optimizer code.
Description: mysql> show create table ashe\G *************************** 1. row *************************** Table: ashe Create Table: CREATE TABLE `ashe` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `name_1` varchar(100) NOT NULL DEFAULT 'cccccccccccccc', PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=262121 DEFAULT CHARSET=utf8mb4 COMMENT='ccc' 1 row in set (0.00 sec) mysql> select count(*),name from ashe group by name; +----------+------+ | count(*) | name | +----------+------+ | 147455 | aaa | | 1 | bbb | +----------+------+ 2 rows in set (0.61 sec) mysql> explain select * from ashe.ashe where name ='aaa'; +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+ | 1 | SIMPLE | ashe | NULL | ref | name | name | 43 | const | 73720 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysqld select index name. ashe@macos ~ $ time mysql -S /tmp/mysql_13307.sock -uroot -A -D ashe -e "set profiling=1;select * from ashe where name='aaa';show profiles;show profile for query 1" > /data/1 real 0m3.954s user 0m0.379s sys 0m0.025s ashe@macos ~ $less /data/1 Query_ID Duration Query 1 3.67799900 select * from ashe where name='aaa' Status Duration starting 0.000067 checking permissions 0.000010 Opening tables 0.000221 init 0.000046 System lock 0.000019 optimizing 0.000019 statistics 0.000244 preparing 0.000022 executing 0.000005 Sending data 3.671858 end 0.000015 query end 0.000080 closing tables 0.000022 freeing items 0.000073 logging slow query 0.005276 cleaning up 0.000022 but if we force index primary, ashe@macos ~ $ time mysql -S /tmp/mysql_13307.sock -uroot -A -D ashe -e "set profiling=1;select * from ashe force index(primary) where name='aaa';show profiles;show profile for query 1" > /data/2 real 0m1.249s user 0m0.383s sys 0m0.020s Query_ID Duration Query 1 0.98165800 select * from ashe force index(primary) where name='aaa' Status Duration starting 0.000091 checking permissions 0.000014 Opening tables 0.000154 init 0.000060 System lock 0.000024 optimizing 0.000023 statistics 0.000040 preparing 0.000029 executing 0.000006 Sending data 0.981117 end 0.000015 query end 0.000021 closing tables 0.000020 freeing items 0.000027 cleaning up 0.000017 How to repeat: CREATE TABLE `ashe` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `name_1` varchar(100) NOT NULL DEFAULT 'cccccccccccccc', PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB for(int I=0;i<=147455;i++){ insert into ashe(name) values(aaa); } update ashe set name='bbb' where id=1; and run my test as Description. Suggested fix: I will give some information after view optimizer code.