Удаление временного дерева B Сортировка из SQLite-запроса

У меня есть довольно простая реализация службы загрузки изображений, где вы можете загружать изображения и отмечать их. Это моя схема:

CREATE TABLE Tag(
    orm_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    pid_high UNSIGNED BIG INT NOT NULL, 
    pid_low UNSIGNED BIG INT NOT NULL, 
    name STRING NOT NULL, 
    CONSTRAINT KeyConstraint UNIQUE (pid_high, pid_low) ON CONFLICT FAIL);

CREATE TABLE TagBridge(
    orm_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    pid_high UNSIGNED BIG INT NOT NULL, 
    pid_low UNSIGNED BIG INT NOT NULL, 
    image_id_high UNSIGNED BIG INT NOT NULL, 
    image_id_low UNSIGNED BIG INT NOT NULL, 
    tag_id_high UNSIGNED BIG INT NOT NULL, 
    tag_id_low UNSIGNED BIG INT NOT NULL, 
    CONSTRAINT KeyConstraint UNIQUE (pid_high, pid_low) ON CONFLICT FAIL);

CREATE TABLE Image(
    orm_id INTEGER PRIMARY KEY AUTOINCREMENT, 
    pid_high UNSIGNED BIG INT NOT NULL,
    pid_low UNSIGNED BIG INT NOT NULL, 
    filehash STRING NOT NULL, 
    mime STRING NOT NULL, 
    uploadedDate INTEGER NOT NULL, 
    ratingsAverage REAL, 
    CONSTRAINT KeyConstraint UNIQUE (pid_high, pid_low) ON CONFLICT FAIL);

И индексы

CREATE INDEX ImageTest on Image(pid_high, pid_low, uploadedDate DESC);
CREATE INDEX ImagefilehashIndex ON Image (filehash);
CREATE INDEX ImageuploadedDateIndex ON Image (uploadedDate);
CREATE INDEX TagnameIndex ON Tag (name);

Причина, по которой есть поля pid_high/pid_low вместо стандартного первичного ключа, заключается в том, что эта служба использует клиентские авторизированные 128-битные GUID, но это не сильно влияет на скорость запроса.

Так как это интернет, подавляющее большинство изображений на этой службе - это кошки, и они помечены как "кошка". Фактически, около 47 000 из 50 000 изображений помечены "кошкой". Запрос на получение всех изображений с меткой "cat" -

select i.* from Tag t, TagBridge b, Image i 
where 
    b.tag_id_high = t.pid_high AND b.tag_id_low = t.pid_low 
AND b.image_id_high = i.pid_high and b.image_id_low = i.pid_low 
AND t.name ='cat' 
order by uploadedDate DESC LIMIT 20;

План запроса для этого -

sele  order          from  deta
----  -------------  ----  ----
0     0              0     SEARCH TABLE Tag AS t USING INDEX TagnameIndex (name=?) (~1 rows)
0     1              1     SCAN TABLE TagBridge AS b (~472 rows)
0     2              2     SEARCH TABLE Image AS i USING INDEX ImageTest (pid_high=? AND pid_low=?) (~1 rows)
0     0              0     USE TEMP B-TREE FOR ORDER BY

Основная проблема здесь - последняя строка, USE TEMP B-TREE FOR ORDER BY. Это значительно замедляет запрос. Без предложения "order by" весь запрос занимает около 0.001s для запуска. С предложением order by запрос занимает 0,483 с, что представляет собой штраф за производительность в 400 раз.

Я хотел бы получить этот запрос менее 0,1 секунды, но я не уверен, как это сделать. Я пробовал много других запросов и добавлял и удалял индексы, но это самый быстрый, который я смог запустить.

Ответ 1

Это общая проблема выбора между индексом фильтрации и упорядочения:

Выбор индекса

Вы должны сохранить список популярных тегов (для которых индекс упорядочения более выгоден) и как-то запретить индекс фильтрации, если тег популярен, скажем так:

SELECT  i.*
FROM    Tag t, TagBridge b, Image i 
WHERE   b.tag_id_high = t.pid_high AND b.tag_id_low = t.pid_low 
        AND b.image_id_high = i.pid_high AND b.image_id_low = i.pid_low 
        AND t.name || '' = 'cat' 
ORDER BY
        i.uploadedDate DESC
LIMIT 20

В качестве альтернативы вы можете денормализовать свою схему и добавить uploadedDate в TagBridge, заполнив ее триггером или любым другим. Затем создайте составной индекс на TagBridge (pid_high, pid_low, uploadedDate, image_id_high, image_id_low) и немного перепишите запрос:

SELECT  i.*
FROM    TagBridge b, Image i
WHERE   b.tag_id_high =
        (
        SELECT  t.pid_high
        FROM    Tag t
        WHERE   t.name = 'cat'
        )
        AND b.tag_id_low =
        (
        SELECT  t.pid_low
        FROM    Tag t
        WHERE   t.name = 'cat'
        )
        AND i.pid_high = b.image_id_high
        AND i.pid_low = b.image_id_low
ORDER BY
        b.uploadedDate DESC
LIMIT 20;

Двойной подзапрос связан с тем, что SQLite не понимает синтаксис кортежа.