teamtnt / tntsearch

A fully featured full text search engine written in PHP
https://tnt.studio/solving-the-search-problem-with-laravel-and-tntsearch
MIT License
3.1k stars 291 forks source link

[MySQL engine ] Add hit_count to doclist database index #318

Closed BlackbitDevs closed 2 months ago

BlackbitDevs commented 2 months ago

Currently there is this index: https://github.com/teamtnt/tntsearch/blob/a763e66ca1bdebf1fab8f9ed10ec4bdbe2682bb0/src/Engines/MysqlEngine.php#L74

For retrieving all documents for a certain term the following query gets used: https://github.com/teamtnt/tntsearch/blob/a763e66ca1bdebf1fab8f9ed10ec4bdbe2682bb0/src/Engines/MysqlEngine.php#L385

With the current index, this needs to sort the results with filesort:

Bildschirmfoto 2024-09-03 um 07 33 32

When we also add hit_count to the index, the filesort is not necessary anymore but filtering and sorting gets fully covered by the index:

Bildschirmfoto 2024-09-03 um 07 36 50

This results in better performance.

There is no drawback for other queries which only filter by term_id as MySQL in this case can also use the index prefix (in this case only the column term_id).