Tribler / tribler

Privacy enhanced BitTorrent client with P2P content discovery
https://www.tribler.org
GNU General Public License v3.0
4.74k stars 445 forks source link

Fix slow database queries, by using partial index `idx_torrentstate__last_check__partial` #7955

Closed kozlovsky closed 3 months ago

kozlovsky commented 3 months ago

This PR fixes #7954 by adding the exact SQL condition has_data = 1 to queries to the TorrentState table. It allows SQLite query optimizer to use a partial index on TorrentState table, defined as

    CREATE INDEX IF NOT EXISTS idx_torrentstate__last_check__partial
    ON TorrentState (last_check, seeders, leechers, self_checked)
    WHERE has_data = 1;

As a result, the speed of queries issued by the torrent checker icreases drastically, from 4 seconds to 0.04 seconds.