Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0, 11.1, 11.2, 11.3, 11.4.0
-
Linux
Description
I compared the "explain" output of two "select count
from table", identical tables, one mariadb and the other "mysql 8.3", and they are different. The tables have one single field, varchar(10) and it's the primary key. I need to understand the difference:
Mariadb:
explain extended select count from goodnumber force index(primary);
+------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-------+
|
| 1 | SIMPLE | goodnumber | ALL | NULL | NULL | NULL | NULL | 315011080 | 100.00 | |
|
+------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-------+,
|
Mysql, explain select count(*) from goodnumber;
|
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-----------+----------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-----------+----------+-------------+
|
| 1 | SIMPLE | goodnumber | NULL | index | NULL | PRIMARY | 42 | NULL | 315054340 | 100.00 | Using index |
|
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-----------+----------+-------------+.
|
The fastest result is the latter, like 10 times. Does it mean that the commercial version of MySQL by Oracle has a better optimizer and it counts the records on a table about 10 times faster?
Attachments
Issue Links
- relates to
-
MDEV-5004 Support parallel read transactions on the same snapshot
-
- Open
-
-
-
- Open
-
-
-
- In Review
-