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

Question

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

On the actual data, and your hardware, and software. Again, what did you measure? — CL.
– CL., Commented Jan 9, 2016 at 15:43
I gave you the db structure, and what i want is to find the best index way to archive the best performance. if i've done the measuring, i don't have to ask which index way is the best. — TomSawyer
– TomSawyer, Commented Jan 9, 2016 at 15:50
The DB structure is not enough (otherwise, ANAYLZE would not exist). Answering this would require more explanations that appropriate on SO. — CL.
– CL., Commented Jan 9, 2016 at 18:16

Rick James · Accepted Answer · 2016-01-10 04:40:39Z

-1

These comments apply to MySQL.

First of all, you cannot index TEXT. You can index VARCHAR up to some limit (usually 255). So, where practical, change TEXT to some reasonable length VARCHAR(n).

Let's look at your examples:

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

Those four are essentially all the same: The WHERE has only '=' AND'd together. So start a composite index with those variables in any order. Since you finished the WHERE and did not have a GROUP BY, you can move on to the ORDER BY. Add freq to the end. The last one, for example, needs

INDEX(kd3, dt, dm, freq) or
INDEX(dm, dt, kd4, freq) or ...

This case is different because of the LIKE:

SELECT * FROM ng1 WHERE kd3='abc' AND word1 LIKE 'abc%' AND dt='1' AND dm='1' ORDER BY freq DESC

The LIKE without a leading wildcard is considered a "range", sort of like word1 >= 'abc' AND word1 < 'abd'. As with ORDER BY, a range will be the last column used in an index. So, the best you can do is:

INDEX(kd3, dt, dm, word1)

The first 3 can be in any order, but word1 must come after them. Adding freq will be of no use.

I discuss this and more in my Index Cookbook.

So, in your example, 5 different indexes for the 5 SELECTs is optimal. If, for whatever reason, you wanted to minimize the number of indexes, here are two approaches:

Sometimes the optimizer will be happy with skipping the WHERE and using an index for the ORDER BY. In that case INDEX(freq) would be "better than nothing" for all 5.

Your question suggested some "consolidating". I would suggest INDEX(dt, dm, kd3), in exactly that order, would handle the last 4 SELECTs "better than nothing". dt has to be first for #2. kd3 was added because it helps #4 and #5.

Other notes...

NULL/NOT NULL does not matter much.

PRIMARY KEY(word1) is "better than nothing", but only for #5. And, depending on cardinality, it might be used in preference over the one I suggested.

Write operations (INSERT, DELETE, and sometimes UPDATE) are slowed down by having lots of indexes. However, usually the benefit to the SELECTs outweighs this. So, as long as you don't "index every column", don't worry about the number of indexes.

answered Jan 10, 2016 at 4:40

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TomSawyer Over a year ago

In SQLite, there is no varchar field, and i've tried to index text field in SQLite and it works, it runs faster than without index. So the answer is creating every single particular index for each query with multiple columns?

TomSawyer Over a year ago

It would create too much indexes. Anyway to used to it with less index field? For example: SELECT * FROM ng1 WHERE kd1='a' AND dt=any AND dm = any ?

Rick James Over a year ago

Any index starting with kd1 may help that query. That is, even INDEX(kd1) may be worth having. There is a tradeoff between having the 'best' indexes versus having 'too many' indexes. And 'caching' comes into play -- perhaps that is why it happened to run faster that time without the index.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related