Actions
Task #96363
closedPerformance optimization for LiveSearch
Status:
Closed
Priority:
Should have
Assignee:
Category:
Performance
Target version:
Start date:
2021-12-15
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
11
PHP Version:
7.4
Tags:
Complexity:
Sprint Focus:
Description
Hello,
TYPO3 LiveSearch uses this query by default for string searches:
WHERE LOWER(column) LIKE "lower-cased-value"
In case of collations like utf8_general_ci (ci stands for case-insensitive) there is no need to lowercase ALL column values. Of cause a case-sensitive search makes sense for collations like utf8_general_cs, uft8_unicode_bin and where TCA ['config']['search']['case'] is true.
You can test performance yourself on MySQL with case-insensitive column collations:
SET profiling = 1; SELECT SQL_NO_CACHE uid, pid, header FROM tt_content WHERE bodytext LIKE "%HeLlO%"; SHOW profile; SELECT SQL_NO_CACHE uid, pid, header FROM tt_content WHERE LOWER(bodytext) LIKE "%hello%"; SHOW profile;
Last query is always slower than the first query.
I will provide a patch.
PS: IMO ['search']['case']-option has never been working.
Stefan
Actions