Comment améliorer les performances de la requête avec WHERE et GROUP BY en sqlite

Question

Il y a environ 2 millions d'enregistrements dans une table appelée log pour le moment. Les performances de la requête ne sont plus acceptables, mais je ne veux pas diviser la table en différentes partitions à l'étape actuelle. Ainsi, j'essaie d'ajouter des index pour améliorer les performances de la requête.

CREATE TABLE log
           (
                id Integer clé primaire autoincrémentée,
                app_id texte,
                __key__id INTEGER,

                secret texte,
                trace_code texte,
                url texte,

                action texte,

                facebook_id texte,
                ip texte,

                tw_time horodatage,
                time horodatage,

                tag texte,
                to_url texte,

                from_url texte,
                referer texte,

                poids entier,
                Unique(app_id, __key__id)
            );
CREATE INDEX key1 sur log (action, url, tag);

Cependant, il semble que SQLite ignore simplement mon index et scanne toute la table. Est-ce que j'ai manqué quelque chose ?

sqlite> expliquer plan de requête sélectionner count(*) from log where action like 'content_%
';
0|0|0|SCAN TABLE log (~1182357 rows)

sqlite> expliquer plan de requête sélectionner count(*) from log where action like 'content_%' group by url, tag;
0|0|0|SCAN TABLE log (~1182357 rows)
0|0|0|UTILISER L'INDEX B-TREE TEMPORAIRE POUR LE GROUP BY

EDIT1

@MaxSem Merci, j'ai essayé et j'ai constaté une nette amélioration lorsque j'ai modifié la requête en :

sqlite> expliquer plan de requête sélectionner count(*) from log indexé par key1 where action in ('content_click','content_mouseover', 'content_display');
0|0|0|RECHERCHER TABLE log EN UTILISANT L'INDEX COUVRANT key1 (action=?) (~886770 rows)
0|0|0|EXECUTER SOUS-REQUÊTE DE LISTE 1

Cependant, je ne peux pas expliquer pourquoi SQLite ne peut pas gérer la requête d'origine.

EDIT2

Je devrais changer ma question. Y a-t-il un moyen d'accélérer ce type de requête dans SQLite ?

Answer 1

Je crois que SQLite ne peut pas utiliser une colonne indexée pour les requêtes avec un prédicat LIKE, même avec des requêtes LIKE commençant par. Mais vous pouvez imiter ce type de prédicat LIKE avec des inégalités :

sqlite> create table t (action text, url text, tag text);
sqlite> insert into t values ('click', 'foo', 'bar');
sqlite> insert into t values ('clack', 'foo', 'bar');
sqlite> insert into t values ('clock', 'foo', 'bar');
sqlite> insert into t values ('cluck', 'foo', 'bar');
sqlite> insert into t values ('cleck', 'foo', 'bar');
sqlite> insert into t values ('clyck', 'foo', 'bar');
sqlite> create index t\_index on t (action, url, tag);

Avec le LIKE commençant par, vous obtenez une analyse complète :

sqlite> explain query plan select count(\*) from t where action like 'cl%';
0|0|0|SCAN TABLE t (~500000 rows)

Mais avec des inégalités, l'index est utilisé :

sqlite> explain query plan select count(\*) from t where action >= 'cl' and action < 'cm';
0|0|0|SEARCH TABLE t USING COVERING INDEX t\_index (action>? AND action

L'inconvénient de cette technique est que vous devez être prudent dans le choix de la borne inférieure ('cm' ici) de sorte que l'ordre lexicographique vous donne ce que vous attendez, et cette technique ne peut pas modéliser facilement tous les prédicats LIKE.

Lorsque vous utilisez un prédicat IN avec des tokens pour correspondre exactement, alors bien sûr l'index est utilisé, car vous revenez à utiliser une égalité :

    sqlite> explain query plan select count(\*) from t where action in ('click', 'clack');
    0|0|0|SEARCH TABLE t USING COVERING INDEX t\_index (action=?) (~20 rows)
    0|0|0|EXECUTE LIST SUBQUERY 1

Si vous voulez toujours utiliser LIKE, vous pouvez joindre votre table avec une liste présélectionnée des valeurs d'action que vous souhaitez à partir d'une sous-requête :

    sqlite> explain query plan select count(\*) from t join (select distinct action from t where action like 'cl%') a where t.action = a.action;
    1|0|0|SCAN TABLE t USING COVERING INDEX t\_index (~500000 rows)
    0|0|1|SCAN SUBQUERY 1 AS a (~500000 rows)
    0|1|0|SEARCH TABLE t USING COVERING INDEX t\_index (action=?) (~10 rows)

Cela vous donne essentiellement un prédicat IN _dynamique_ si vous le souhaitez, au lieu de lister explicitement toutes les valeurs possibles.

Évidemment, ici, j'extrais la liste des actions qui correspondent à la requête LIKE de votre grande table, donc c'est une analyse complète en soi, annulant tous les avantages, mais si vous avez normalisé votre schéma pour avoir une table d'actions séparée (beaucoup plus petite) avec une petite clé de substitution entière que vous utiliseriez comme colonne d'action dans votre table de logs, alors vous éviteriez de stocker plusieurs fois des chaînes répétées (remplacées par de petits entiers) dans votre table de logs, et vous pourriez facilement les joindre pour votre requête.

Answer 2

Vous aurez besoin de :

PRAGMA case_sensitive_like=ON;

Par défaut, SQLite utilise des opérateurs LIKE insensibles à la casse. En général, l'insensibilité à la casse et les index ne s'entendent pas bien. Il est possible pour une base de données d'utiliser un index avec une insensibilité à la casse, mais la complexité supplémentaire et la recherche ne valent pas la peine.

Si vous activez case_sensitive_like, alors SQLite devrait utiliser un index pour votre requête.