Review main Merged

72669[TASK] Use ORDER BY uid DESC in LiveSearch

Stefan Froemken CR +2 V +2 Easy +1 −5 updated 01/07/22, 11:57

Commit message

Sorting query results by text columns may require
a filesort on harddisk which is slow. To boost up
the search results of LiveSearch we have added
an ORDER BY uid DESC to always show the newest
created records first. Column "uid" exists in
all TCA tables and as it is PRIMARY INDEX
sorting will happen in memory.

Review

  • Code review CR +2 Done
    • Christian Kuhn +2
    • Benni Mack +1
  • Verified V +2 Done
    • Christian Kuhn +2
    • Benni Mack +1
    • core-ci +1 CI

Issues 1

Files 1

Review log 1417

  1. Stefan Froemken uploaded patch set 1 12/15/21, 21:27

  2. core-ci voted on patch set 1 V +1 12/15/21, 21:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/21151

  3. Benni Mack commented on patch set 1 12/15/21, 21:41

    I think we could remove the ordering completely
  4. Christian Kuhn commented on patch set 1 12/16/21, 17:23

    I'd prefer an order by uid DESC here, and drop the feature flag again. Result: Last 5 matching records will be shown.
  5. Christian Kuhn commented on patch set 1 CR -1 12/16/21, 17:45

    uid DESC won't work. The scenario is: default_sortby in TCA is set to a text field. In this case order by this field is added. We're not entirely set here. Talking with Stefan about details here, let's see what he comes with.
  6. Stefan Froemken commented on patch set 1 12/23/21, 20:06

    I'm still for removing ORDER BY completely. See following analysis: +----------+------------+-----------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------------------------------------------------+ | 18 | 0.07860025 | SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" | | 19 | 0.08161950 | SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" ORDER BY pid ASC | | 20 | 0.09114525 | SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" ORDER BY details ASC | +----------+------------+-----------------------------------------------------------------------------------------------------------+ Further you can see, that for all queries no index will be used: SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" ORDER BY details ASC +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | sys_log | ALL | NULL | NULL | NULL | NULL | 25243 | Using where; Using filesort | +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" ORDER BY pid ASC +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | sys_log | ALL | NULL | NULL | NULL | NULL | 25243 | Using where; Using filesort | +----+-------------+---------+------+---------------+------+---------+------+-------+-----------------------------+ SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | sys_log | ALL | NULL | NULL | NULL | NULL | 25243 | Using where | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
  7. Stefan Froemken commented on patch set 1 12/23/21, 20:10

    Oh sorry. Just see that you mean ORDER BY uid DESC. I just have tested that, too: +----------+------------+------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------------------------------------------+ | 24 | 0.09476000 | SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" ORDER BY uid DESC | | 25 | 0.09278650 | SELECT SQL_NO_CACHE uid, pid, details FROM sys_log WHERE details LIKE "%logged%" | +----------+------------+------------------------------------------------------------------------------------------------------------+
  8. Stefan Froemken uploaded patch set 2 12/23/21, 20:22

  9. core-ci voted on patch set 2 V +1 12/23/21, 20:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/21441

  10. Christian Kuhn commented on patch set 2 01/06/22, 15:24

    I get your point Stefan. I'm totally with you to improve this situation. Your solution is to ignore TCA sortby and default_sortby entirely. I could live with that. The only drawback is that it makes the result rows non-deterministic - it hands over result set ordering to the DMBS (mysql tends to order by uid implicitly, postgres does not, ...). If we align on the solution to skip TCA sortby and default_sortby evaluation, my suggestion is to come back to my previous idea and simply add 'uid DESC' as sorting: This is quick (uid is primary key, this index will be used, tested this locally already), and it has the additional benefit that the result set is a) deterministic and b) it always shows the latest added rows of the result set, which most likely gives editors results with higher general relevance - it's likely that 'younger' rows are more interesting than older ones. What do you think?
  11. Stefan Froemken commented on patch set 2 01/06/22, 17:45

    In search results I hate ORDER BY in general except of relevance. We don't have any relevance in all these tables. Sorting by uid DESC because customers wants newest records first is a personell interpretation. IMO customer wants the best results at first, but without any relevance we don't have this possibility. So, sorting by uid DESC is only a "fallback", a little gift for the customer like "hey, look, we have ordered the results in a way you don't see" and "we hope you need one of the most current created records first". Ordering by uid DESC is not perfect, I'm not happy, but it is somehow "OK" for me ;-)
  12. Stefan Froemken uploaded patch set 3 01/06/22, 17:59

  13. core-ci voted on patch set 3 V +1 01/06/22, 18:11 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/21727

  14. Benni Mack commented on patch set 3 V +1 CR +1 01/06/22, 18:33

    Fine by me (however, I do prefer relevance in the future too, but that needs more rework than just in LiveSearch :)).
  15. Christian Kuhn commented on patch set 3 01/07/22, 11:56

    Thx Stefan. I guess that's the best we can get with live search currently.
  16. Christian Kuhn voted on patch set 3 V +2 CR +2 01/07/22, 11:57

  17. Christian Kuhn merged patch set 4 01/07/22, 11:57