Issue Feature Closed

#96275Add index to speed up sys_refindex queries

Mathias Bolt Lesniak 12/07/21, 13:19 updated 12/16/21, 13:27

Description

During profiling of DataHandler I noticed a lot of queries like this one:

SELECT `hash` FROM `sys_refindex` WHERE (`tablename` = 'foo_bar') AND (`recuid` = 138) AND (`workspace` = 0)

They were taking quite some time to run with a largish sys_refindex table (1M rows). It turns out there are no indexes supporting this common query, so adding an index speeds up this type of query quite a bit!

CREATE TABLE `sys_refindex` (
    KEY `table_rec_ws` (`tablename`,`recuid`,`workspace`)
);

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.