I have a table like this:
CREATE TABLE `ng1` (
`word1` TEXT NOT NULL,
`kd` TEXT NOT NULL,
`kd1` TEXT NOT NULL,
`kd2` TEXT,
`kd3` TEXT,
`dt` INTEGER DEFAULT '0',
`dm` INTEGER DEFAULT '0',
`freq` REAL DEFAULT '0',
`lastused` INTEGER DEFAULT '0',
PRIMARY KEY(word1)
);
And i have these types of queries:
SELECT * FROM ng1 WHERE kd='abc' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd1='a' AND dt='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd2='ab' AND dt='1' AND dm='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd3='abc' AND dt='1' AND dm='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd3='abc' AND word1 LIKE 'abc%' AND dt='1' AND dm='1' ORDER BY freq DESC
So what kind of index will help me get the best performance?
-
Create multiple indexes for every single field i need to use in query - - index1: kd
- index2: kd1
- index3: kd2 .... and indexes with for kd3,dt,dm,freq field
-
Create multiple indexes with multiple columns:
- index1: kd,freq
- index2: kd1,freq
- index3: kd2,freq
- index4: kd3,freq
- index5: kd3,dt,dm,freq
-
Create single index with multiple columns:
- index1: kd,kd1,kd2,kd3,dt,dm,freq
Aucun commentaire:
Enregistrer un commentaire