Discussion :

[parice02]

Bonjour.
J'ai fait une petite application, une sorte d' "anagrammeur" personnelle. J'utilise python3 comme langage de programmation et le module sqlite3 comme API.
Ma base données contient 2.965.113 mots en différentes langues, toutes les tables sont indexées. J'ai créé une vue "dico_french" qui contient 1.381.064 en français.
Mon soucis est que ma requête met plus de 13 secondes avant de me retourner un résultat.
Voici ma requête SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DISTINCT mot_fr FROM dico_french WHERE mot_fr regexp(?) AND LENGTH(mot_fr) > ? ORDER BY mot_fr ASC

Noté que l'expression "regexp" dans la requête est une fonction écrit en python et exécutable en SQLite grâce à la méthode create_fonction() de la classe sqlite3.Connect qui permet de trier les mots en fonction d'une expression régulière.
J'aimerai savoir si je peux optimiser la requête pour diminuer la durée d’exécution ou si le soucis viens de python.

[acaumes]

Bonjour,

Je crains que l'utilisation de

regexp

n'empêche SQLITE d’utiliser les index ce qui oblige à un parcours complet de la table.

Tu peux essayer d'utiliser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXPLAIN

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXPLAIN QUERY PLAN

pour analyser la charge générée par ta requête et voir les effet de tes modifications.

Cordialement

[parice02]

Merci pour votre réponse.
J'ai exécuté votre proposition.
Voici le résultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
[(1, 0, 1, 'SEARCH TABLE lexemes USING INDEX l_lang_idx (l_lang=?)'),
 (1, 1, 0, 'SEARCH TABLE articles USING INDEX sqlite_autoindex_articles_1 (a_artid=?)'),
 (1, 0, 0, 'USE TEMP B-TREE FOR DISTINCT'),
 (0, 0, 0, 'SCAN SUBQUERY 1'),
 (0, 0, 0, 'USE TEMP B-TREE FOR DISTINCT')]

Les colonnes sont dans cette ordre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

['selectid', 'order', 'from', 'detail']

Pourriez vous m'aider avec l'interprétation?

[acaumes]

Ce qu'on peut voir c'est

* Utilisation d'un index pour une table nommée lexemes
* Utilisation d'un index pour une table nommée articles (et probablement une clé primaire ou une unicité (sqlite_autoindex_articles_1)
* Utilisation d'un arbre pour générer des résultats distincts
* Lecture par parcours complet du résultat (est ce qu'il s'agit de ta vue dico_french)?
* Nouvelle utilisation d'un arbre pour le distinct (tu ne nous montres ni le DDL de tes tables ni celui de ta vue mais est ce que le distinct de ta requête n'est pas redondant avec le distinct de ta vue?)

Donc si tu n'as pas de moyen d'accélérer la fonction regexp (est elle écrite en python?) tu ne pourra probablement pas gagner grand chose.

[parice02]

Voici le SQL de création des tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
CREATE TABLE articles (
	a_alphagram text,
	a_artid int PRIMARY KEY,
	a_title text,
	a_title_flat text,
	a_title_flat_r text,
	a_title_r text,
	a_trans text,
	a_trans_flat text,
	a_trans_flat_r text
);
 
CREATE TABLE defs (
	d_def text,
	d_defid int PRIMARY KEY,
	d_lexid int REFERENCES lexemes(l_lexid),
	d_num int
);
 
CREATE TABLE langs (
	lg_lang varchar(32) PRIMARY KEY,
	lg_num int,
	lg_num_min int
);
 
CREATE TABLE lexemes (
	l_artid int REFERENCES articles(a_artid),
	l_genre text,
	l_is_flexion int,
	l_is_gentile int,
	l_is_locution int,
	l_lang text REFERENCES langs(lg_lang),
	l_lexid int PRIMARY KEY,
	l_num int,
	l_rand int,
	l_sigle text,
	l_type text
);
 
CREATE TABLE prons (
	p_lexid int REFERENCES lexemes(l_lexid),
	p_num int,
	p_pron text,
	p_pron_flat text,
	p_pron_flat_r text,
	p_pronid int
);
 
CREATE TABLE redirects (
	r_target text,
	r_title varchar(255) PRIMARY KEY
);
 
CREATE TABLE transc (
	tr_artid int REFERENCES articles(a_artid),
	tr_transc text,
	tr_transc_flat text,
	tr_transc_flat_r text
);

Voici le code de la fonction regexp écrit en python:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
def regexp(motif, item): 
    """retourne True si le motif regex a été satisfait dans l'item 
       False sinon 
    """ 
    regex = re.compile(motif)  # re.I: ignore casse 
    return regex.search(item) is not None

Voici le SQL de la vue:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT DISTINCT a_artid, a_title FROM articles, lexemes WHERE lexemes.l_artid = articles.a_artid AND lexemes.l_lang = 'fr'

Et voici le code python qui permet l'exécution de la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
with sqlite3.connect("dico_mots.db3") as connexion:
                connexion.create_function("regexp", 2, regexp)
                exp_reg = "^(?!(.*a){4})(?!(.*n){2})(?!(.*g){2})(?!(.*r){2})(?!(.*m){3})(?!(.*e){2})[angrme]$"
                liste = []
                curseur = connexion.execute("""SELECT DISTINCT mot_fr
                        FROM dico_french
                        WHERE mot_fr regexp(?)
                        ORDER BY mot_fr ASC""", (exp_reg,))
                liste = curseur.fetchall()

[acaumes]

As tu essayé de compiler ton motif

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

regex = re.compile(motif)  # re.I: ignore casse

une seule fois (si c'est possible en python)?

Je penses que tu gagnerais pas mal car je crois que la compilation de expression régulières est assez coûteuse...

[Waldar]

Vous pouvez remplacer le code de la vue par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select a_artid, a_title
  from articles
 where exists (select null
                 from lexemes
                where lexemes.l_artid = articles.a_artid
                  and lexemes.l_lang  = 'fr');

Vous économisez ainsi le distinct.