Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.5
-
None
-
Debian 8, x86_64
Description
Using the following table:
CREATE TABLE `obj` ( |
`partition` smallint(5) unsigned NOT NULL, |
`oid` bigint(20) unsigned NOT NULL, |
`tid` bigint(20) unsigned NOT NULL, |
`data_id` bigint(20) unsigned DEFAULT NULL, |
`value_tid` bigint(20) unsigned DEFAULT NULL, |
PRIMARY KEY (`partition`,`tid`,`oid`), |
KEY `partition` (`partition`,`oid`,`tid`), |
KEY `data_id` (`data_id`) |
) ENGINE=ROCKSDB
|
The following request is far too slow:
MariaDB [neo0]> analyze SELECT SQL_NO_CACHE `partition`, MAX(tid) FROM obj WHERE `partition`=0;
|
+------+-------------+-------+------+-------------------+-----------+---------+-------+--------+-----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+-------------------+-----------+---------+-------+--------+-----------+----------+------------+-------------+
|
| 1 | SIMPLE | obj | ref | PRIMARY,partition | partition | 2 | const | 650282 | 647396.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+-------+------+-------------------+-----------+---------+-------+--------+-----------+----------+------------+-------------+
|
1 row in set (0.40 sec)
|
It used the wrong index (maybe another issue). With correct index, it's not faster:
MariaDB [neo0]> analyze SELECT SQL_NO_CACHE `partition`, MAX(tid) FROM obj FORCE INDEX (PRIMARY) WHERE `partition`=0;
|
+------+-------------+-------+------+---------------+---------+---------+-------+--------+-----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+---------+---------+-------+--------+-----------+----------+------------+-------------+
|
| 1 | SIMPLE | obj | ref | PRIMARY | PRIMARY | 2 | const | 664465 | 647396.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+-------+------+---------------+---------+---------+-------+--------+-----------+----------+------------+-------------+
|
1 row in set (0.38 sec)
|
I want the `partition` column in the result because I'd like to query MAX(tid) for several values of `partition` at the same time (using GROUP BY). Without this column, it works as expected:
MariaDB [neo0]> SELECT SQL_NO_CACHE MAX(tid) FROM obj WHERE `partition`=0;
|
+--------------------+
|
| MAX(tid) |
|
+--------------------+
|
| 270055958963668676 |
|
+--------------------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- is part of
-
MDEV-11588 Support for ONLY_FULL_GROUP_BY functional dependency
-
- Stalled
-