Project

General

Profile

Actions

Feature #100296

closed

Add DB Index to sys_news

Added by Stefan Froemken over 1 year ago. Updated over 1 year ago.

Status:
Closed
Priority:
Should have
Category:
Performance
Target version:
Start date:
2023-03-24
Due date:
% Done:

0%

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

Description

Hello,

the query of sys_news is not using index. With an index you can "speed up" the query from 0.004 to 0.0005 seconds.

Stefan

Actions #1

Updated by Gerrit Code Review over 1 year ago

  • Status changed from New to Under Review

Patch set 1 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/78252

Actions #2

Updated by Stefan Froemken over 1 year ago

It's this query:

SELECT
  `uid`,
  `title`,
  `content`,
  `crdate`
FROM
  `sys_news`
WHERE
  (
    (`sys_news`.`deleted` = 0)
    AND (`sys_news`.`hidden` = 0)
    AND (
      `sys_news`.`starttime` <= 1679690460
    )
    AND (
      (
        (`sys_news`.`endtime` = 0)
        OR (
          `sys_news`.`endtime` > 1679690460
        )
      )
    )
  )
ORDER BY
  `crdate` DESC;

Actions #3

Updated by Gerrit Code Review over 1 year ago

Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/78252

Actions #4

Updated by Stefan Froemken over 1 year ago

  • Status changed from Under Review to Closed

The columns starttime, endtime, hidden and deleted are in 98% always 0. So the QueryParser decides: reading all data from disc is faster than reading and checking values from index. As no index is used now, it also make no sense to create an index on just crdate.

Actions

Also available in: Atom PDF