Bug #73167
closed
Indexes on deleted and hidden
0%
Description
Doesn't it make sense to define an index on the fields "deleted" and "hidden" to improve performance?
KEY visibility (deleted,hidden)
99% of the queries for table "pages" and "tt_content" contain "WHERE deleted=0 AND hidden=0" so this index should speed these queries up.
Updated by Mathias Schreiber over 8 years ago
- Tracker changed from Suggestion to Bug
- Project changed from 3098 to TYPO3 Core
Updated by Christian Kuhn over 8 years ago
- Is Regression set to No
pages has a key on (pid,deleted,sorting) that typically kicks in and drastically reduces the result set. this was researched quite often already.
same for tt_content, the typically used key is KEY parent (pid,sorting) that reduces the result set to elements on a specific page. with a typical set of < 100 elements on one page a manual scan of the result is then quicker than maintaining further indexes.
did you measure anything, yet, or is this issue just a "i thought it might be wise?". which exact queries do you refer to? in short, new indexes will only be accepted for typical use cases that really lead to a significant benefit for a majority of sites.
Updated by Sven Burkert over 8 years ago
It's just "I have not much experience with MySQL performance improvements but think, it might be wise" ;) And because I found no other ticket according this topic, I've created this one.
I think it might be wise because...
...if a website grows over time, I think 50% or more of it contents (pages and tt_content) will have status "deleted=1" or "hidden=1"
...nearly all of the database queries for these tables contain "deleted=0 AND hidden=0"
Updated by Morton Jonuschat about 8 years ago
- Status changed from New to Closed
Closing this as adding the key on deleted and hidden alone does not provide any value since most queries also restrict on either UID or PID.
In the PID case the parent index gets used as it eliminates most other records due to pid and deleted being indexes.
In the UID case it's the primary key which already reduces the number of queried rows to 1.
Adding and Index on pid, deleted and hidden doesn't provide a noticeable benefit either since the biggest part of pages will already be filtered by pid and deleted, so it's down to the hidden field, but that's just a few rows that need comparing which isn't noticeably improved by the index.
If you think that this is the wrong decision or experience this issue again, then please get into contact on slack, write to the mailing list typo3.teams.bugs with issue number and an explanation or open a new ticket and add a relation to this ticket number.