Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
N/A
-
None
Description
There was some effort made in 10.7 to store UUID efficiently (MDEV-26664).
Apparently the logic does not apply (or doesn't apply well enough) to descending indexes which are being introduced in 10.8 in the scope of MDEV-13756.
Below is based on type_uuid.type_uuid_myisam test.
CREATE TABLE t1 (a UUID, KEY(a)) ENGINE=MyISAM; |
|
|
--delimiter $
|
FOR i IN 0..8191 DO |
INSERT INTO t1 VALUES (UUID()); |
END FOR $ |
--delimiter ;
|
|
|
SELECT
|
INDEX_LENGTH, DATA_LENGTH, INDEX_LENGTH/DATA_LENGTH
|
FROM INFORMATION_SCHEMA.TABLES |
WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; |
|
|
DROP TABLE t1; |
With a standard ascending key it gives the result
INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH
|
79872 139264 0.5735
|
With a descending index (KEY(a DESC)) it produces
|
preview-10.8-MDEV-13756-desc-indexes c10e10c6 |
INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH
|
150528 139264 1.0809
|
Which, according to the same test's criteria, is a middle ground between "packed" and "not packed".
Attachments
Issue Links
- is caused by
-
MDEV-13756 Implement descending index: KEY (a DESC, b ASC)
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is caused by |
| Link |
This issue relates to |
| Description |
There was some effort made in 10.7 to store UUID efficiently ( Apparently the logic does not apply (or doesn't apply well enough) to descending indexes which are being introduced in 10.8 in the scope of Below is based on {{type_uuid.type_uuid_myisam}} test. {code:sql} CREATE TABLE t1 (a UUID, KEY(a)) ENGINE=MyISAM; --delimiter $ FOR i IN 0..8191 DO INSERT INTO t1 VALUES (UUID()); END FOR $ --delimiter ; SELECT INDEX_LENGTH, DATA_LENGTH, INDEX_LENGTH/DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; DROP TABLE t1; {code} With a standard ascending key it gives the result {code:sql} INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH 79872 139264 0.5735 {code} With a descending index ({{KEY(a DESC)}}) it produces {code:sql} INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH 150528 139264 1.0809 {code} Which, according to the same test's criteria, is a middle ground between "packed" and "not packed". |
There was some effort made in 10.7 to store UUID efficiently ( Apparently the logic does not apply (or doesn't apply well enough) to descending indexes which are being introduced in 10.8 in the scope of Below is based on {{type_uuid.type_uuid_myisam}} test. {code:sql} CREATE TABLE t1 (a UUID, KEY(a)) ENGINE=MyISAM; --delimiter $ FOR i IN 0..8191 DO INSERT INTO t1 VALUES (UUID()); END FOR $ --delimiter ; SELECT INDEX_LENGTH, DATA_LENGTH, INDEX_LENGTH/DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; DROP TABLE t1; {code} With a standard ascending key it gives the result {code:sql} INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH 79872 139264 0.5735 {code} With a descending index ({{KEY(a DESC)}}) it produces {code:sql|title=preview-10.8- INDEX_LENGTH DATA_LENGTH INDEX_LENGTH/DATA_LENGTH 150528 139264 1.0809 {code} Which, according to the same test's criteria, is a middle ground between "packed" and "not packed". |
| Summary | Descending UUID index is not stored as efficiently as ascending | MyISAM descending index compression is not as efficiently as ascending |
| Summary | MyISAM descending index compression is not as efficiently as ascending | MyISAM/Aria descending index compression is not as efficiently as ascending |
| Assignee | Alexander Barkov [ bar ] | Sergei Golubchik [ serg ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Resolution | Won't Fix [ 2 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Summary | MyISAM/Aria descending index compression is not as efficiently as ascending | MyISAM/Aria descending index compression is not as efficient as ascending |