TYPO3 Core

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