[MDEV-32125] Improvement Created: 2023-09-07  Updated: 2023-09-08

Status: Open
Project: MariaDB Server
Component/s: Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: Aurélien LEQUOY Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

My goal there will be to add an hidden index to get last availabe value.

We have a table with few values, but an history of 1 000 000 of lines.

select * from mysql_server;
 
...
 
23 rows in set (16,225 sec)

if specifiy all id from the PK

MariaDB [pmacontrol]> select id from mysql_server;
 
+----+
 
| id |
 
+----+
 
|  1 |
 
|  2 |
 
|  3 |
 
|  4 |
 
| 24 |
 
|  5 |
 
|  6 |
 
|  7 |
 
|  8 |
 
|  9 |
 
| 10 |
 
| 11 |
 
| 12 |
 
| 13 |
 
| 14 |
 
| 15 |
 
| 16 |
 
| 18 |
 
| 19 |
 
| 20 |
 
| 21 |
 
| 22 |
 
| 23 |
 
+----+
 
23 rows in set (1,070 sec)

=> a bit long but better

then select all and specify the ids :

 select * from mysql_server where id in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24);
 
....
 
23 rows in set (0,001 sec)

it's really fast, so I believe there is a way to improve the optimizer with response time

 Comments   
Comment by Sergei Golubchik [ 2023-09-08 ]

you mean, you have a system versioned table? if it has a lot of history, it will blow up the index and that will slow down the access. This is expected. To solve this MariaDB supports tables partitioned by system time. This will keep the current data in one partition, which will be small, and history data separately. So when you don't specify AS OF MariaDB will only use the current partition and the speed will pretty much be as if there was no versioning at all.

Generated at Thu Feb 08 10:29:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.