Project

General

Profile

Actions

Task #96365

closed

Performance: Add feature flag to disable order by for LiveSearch

Added by Stefan Froemken almost 3 years ago. Updated about 2 years ago.

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

100%

Estimated time:
TYPO3 Version:
12
PHP Version:
7.4
Tags:
Complexity:
Sprint Focus:

Description

Hello,

I just see that LiveSearch adds an ORDER BY for its search results. This is a huge performance issue. Here an example:

SET profiling = 1;

SELECT SQL_NO_CACHE uid, details
FROM `sys_log`
WHERE details LIKE "%logged%" 
ORDER BY details
LIMIT 5;
SHOW profile;

SELECT SQL_NO_CACHE uid, details
FROM `sys_log`
WHERE details LIKE "%logged%" 
LIMIT 5;

SHOW profile;

1st query needs 0.21 seconds
2nd query needs 0.0007 seconds

Why?

That's easy: If you add ORDER BY the database has to search ALL records and order them on HDD (see filesort in EXPLAIN output). If you remove ORDER BY the database will stop searching, if the LIMIT is reached.

IMO ordering of search results does not make sense. Remember: It's a LIKE search. So your result can be everywhere in the range of 0-9A-Z.

I will create a patch to disable the ORDER BY statement.

Stefan

Actions #1

Updated by Gerrit Code Review almost 3 years 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/+/72669

Actions #2

Updated by Gerrit Code Review almost 3 years 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/+/72669

Actions #3

Updated by Gerrit Code Review almost 3 years ago

Patch set 3 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/+/72669

Actions #4

Updated by Gerrit Code Review almost 3 years ago

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

Actions #5

Updated by Stefan Froemken almost 3 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions #6

Updated by Benni Mack about 2 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF