Project

General

Profile

Actions
Copy link

Feature #96275

closed

Add index to speed up sys_refindex queries

Added by Mathias Bolt Lesniak almost 3 years ago. Updated almost 3 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Performance
Target version:
-
Start date:
2021-12-07
Due date:
% Done:

0%

Estimated time:
PHP Version:
Tags:
Complexity:
no-brainer
Sprint Focus:

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`)
);

Files

lookup-rec-key.png (467 KB) lookup-rec-key.png Christoph Lehmann, 2021-12-12 11:44
Actions
Copy link
 #1

Updated by Christoph Lehmann almost 3 years ago

For me the key lookup_rec is used. Do you have it?

Actions
Copy link
 #2

Updated by Christian Kuhn almost 3 years ago

  • Status changed from New to Needs Feedback

Indeed, existing key lookup_rec should cover this.

Actions
Copy link
 #3

Updated by Mathias Bolt Lesniak almost 3 years ago

Yup, turns out the performance issues were caused by a corrupt table. This issue can be closed.

Actions
Copy link
 #4

Updated by Christian Kuhn almost 3 years ago

  • Status changed from Needs Feedback to Closed

Thanks for feedback.

Actions
Copy link

Also available in: Atom PDF