Как ускорить выполнение запроса?

Question

[RimMirK]

однофайловая (database.db) бд sqlite3, вызов через питон, либа sqlite3. управляю через sqlite studio
Есть вот такой запрос который достает картинку, и небольшую сводку. Сейчас, с ~ 3.5 к строк запрос выполняется ~ 2.5 секунды. Можно его как-то ускорить? Методом проб я понял что COUNT(1) выполняется немного быстрее чем с другим аргументом. Может, можно чем-то заменить вложенные запросы?

SELECT  id, m.data, m.user_id, m.status,
        (SELECT COUNT(1) FROM likes    l WHERE l.meme_id = m.id),
        (SELECT COUNT(1) FROM comments c WHERE c.meme_id = m.id),
        (SELECT COUNT(1) FROM saves    s WHERE s.meme_id = m.id),
        (SELECT COUNT(1) FROM likes    l WHERE l.user_id = u.id AND l.meme_id = m.id),
        (SELECT COUNT(1) FROM views    v WHERE v.meme_id = m.id)
FROM memes m, users u WHERE u.id = 1 AND m.status = 'accepted' ORDER BY RANDOM() LIMIT 1;

структуры таблиц

коменты

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    meme_id INTEGER NOT NULL,
    text    TEXT    NOT NULL
);

лайки

CREATE TABLE likes (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    meme_id INTEGER NOT NULL
);

сохранения

CREATE TABLE saves (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    meme_id INTEGER NOT NULL
);

просмотры

CREATE TABLE views (
    id      INTEGER  PRIMARY KEY
                     NOT NULL,
    user_id INTEGER  NOT NULL,
    meme_id INTEGER  NOT NULL,
    time    DATETIME DEFAULT (DATETIME(CURRENT_TIMESTAMP, '+02:00') ) 
);

сами мемы

CREATE TABLE memes (
    id                   INTEGER  PRIMARY KEY,
    user_id              INTEGER  NOT NULL,
    data                 BLOB     NOT NULL,
    upload_time          DATETIME DEFAULT (DATETIME(CURRENT_TIMESTAMP, '+02:00') ),
    status               TEXT,
    updating_status_time DATETIME,
    removal_time         DATETIME,
    deletion_reason      TEXT
);

и пользователи

CREATE TABLE users (
    id          INTEGER PRIMARY KEY
                        NOT NULL,
    tg_id       INTEGER NOT NULL,
    fname       TEXT,
    lname       TEXT,
    uname       TEXT,
    nickname    TEXT,
    status      TEXT    DEFAULT user
                        NOT NULL,
    check_views BOOLEAN NOT NULL
                        DEFAULT (true) 
);

UPD: Методом тыка выяснил что тормозит m.status = 'accepted'

Comments

[RimMirK]

заменил на 5 строке count на id, и запрос стал выполнятся 0.8 секунд.
На этой строке я проверяю лайкнут мем, или нет

Answer 1

[tukreb]

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

SELECT likes, comments, saves, likes, views    
FROM meta_meme_table

Разумеется вам придётся добавить код, которые будет следить, чтобы данные в мета таблицы отражали действительность. Например, если удалили лайк, вам придётся добавить метод, которые сделает перерасчёт лайков в мета таблице. Или делать это по крону.

Comments

[RimMirK]

то есть, создать таблицу с примерно такими полями?

meta_memes
id | meme_id | likes_count | comments_count | saves_count | ...
-----------------------------------------------------------------------
1  | 2745    | 4           | 2              | 0           |

[tukreb]

RimMirK, да. И когда кто-то лайкает или коментит, в эту таблицу тоже делать +1 или -1.
И конечно лучше сделать запасной метод, который сделает перерасчёт, так как данные иногда могут расходиться. И вызывать его когда у вас появляются сомнения в точности данных, но такое не должно случаться, если правильно всё продумали.

Answer 2

[alexalexes]

FROM memes m, users u
У вас таблицы не связаны между собой, нужно, что-то такое:

FROM memes m
join users u on u.id = m.user_id

Или если join боитеся, то так:

FROM memes m, users u 
where u.id = m.user_id -- про соединение в where (эквивалент on) не забываем

m.status = 'accepted'
Для этой бы колонки индекс бы создать.

Comments

[RimMirK]

бред полнейший.
u.id это айди юзера который смотрит мем, а m.user_id это автор мема

[alexalexes]

Ну, тогда Users должен уйти в подзапрос.

SELECT  id, m.data, m.user_id, m.status,
        (SELECT COUNT(1) FROM likes    l WHERE l.meme_id = m.id),
        (SELECT COUNT(1) FROM comments c WHERE c.meme_id = m.id),
        (SELECT COUNT(1) FROM saves    s WHERE s.meme_id = m.id),
        (SELECT COUNT(1) FROM likes    l
                         join users u on l.user_id = u.id
                          and  u.id = 1
                        WHERE l.meme_id = m.id),
        (SELECT COUNT(1) FROM views    v WHERE v.meme_id = m.id)
FROM memes m
where m.status = 'accepted' 
ORDER BY RANDOM() LIMIT 1;

[RimMirK]

alexalexes, https://www.youtube.com/watch?v=0b4QJRHkwmc