I'm trying to optimize a SQL Query by creating indexes to have the best performances.
Table definition
CREATE TABLE Mots (
numero INTEGER NOT NULL,
fk_dictionnaires integer(5) NOT NULL,
mot varchar(50) NOT NULL,
ponderation integer(20) NOT NULL,
drapeau varchar(1) NOT NULL,
CONSTRAINT pk_mots PRIMARY KEY(numero),
CONSTRAINT uk_dico_mot_mots UNIQUE(fk_dictionnaires, mot),
CONSTRAINT fk_mots_dictionnaires FOREIGN KEY(fk_dictionnaires) REFERENCES Dictionnaires(numero)
);
Indexes definition
CREATE INDEX idx_dictionnaires ON mots(fk_dictionnaires DESC);
CREATE INDEX idx_mots_ponderation ON mots(ponderation);
CREATE UNIQUE INDEX idx_mots_unique ON mots(fk_dictionnaires, mot);
SQL Query :
SELECT numero, mot, ponderation, drapeau
FROM mots
WHERE mot LIKE 'ar%'
AND fk_dictionnaires=1
AND LENGTH(mot)>=4
ORDER BY ponderation DESC
LIMIT 5;
Query Plan
0|0|0|SEARCH TABLE mots USING INDEX idx_dictionnaires (fk_dictionnaires=?) (~2 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
Defined indexes don't seem used and the query lasts (according to the .timer) :
CPU Time: user 0.078001 sys 0.015600
However, when I removed the fk_dictionnaires=1. My indexes are correctly used and the performances are around 0.000000-0.01XXXXXX sec
0|0|0|SCAN TABLE mots USING INDEX idx_mots_ponderation (~250000 rows)
I found out some similars questions on stackoverflow but no anwser help me.
How can I improve the performances by using indexes or/and by changing the SQL Query? Thanks in advance.