samedi 9 janvier 2016

SQLite: Create multiple indexes , multiple index columns vs single index which one will help to get the best performance?

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?

  1. 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
  2. Create multiple indexes with multiple columns:

    • index1: kd,freq
    • index2: kd1,freq
    • index3: kd2,freq
    • index4: kd3,freq
    • index5: kd3,dt,dm,freq
  3. Create single index with multiple columns:

    • index1: kd,kd1,kd2,kd3,dt,dm,freq

Aucun commentaire:

Enregistrer un commentaire