Issue Task Closed

#96363Performance optimization for LiveSearch

Stefan Froemken 12/15/21, 20:14 updated 07/07/23, 12:29

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

Reviews 1