#96363Performance optimization for LiveSearch
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