CODEWITHSUNDEEP
sqlsqlite
alancc
alancc

Reputation: 799

Which columns should I create Index?

I am using SQLite 3. I have a table MyTable, as follows:

    Create table mytable (a as INTEGER, b as INTEGER, c as INTGER, c as INTEGER);

    Insert into mytable (a,b,c,d) values (1, 1,1,1);
    Insert into mytable (a,b,c,d) values (1, 2,1,2);
    Insert into mytable (a,b,c,d) values (2, 1,1,3);
    Insert into mytable (a,b,c,d) values (2, 3,2,1);
    Insert into mytable (a,b,c,d) values (3, 1,2,3);

After adding data, I need to perform the following query frequently:

    Select * from mytable where (a = ##) and (b = ##) and (c = ##) and (d < ##);

In such a case, which columns should be used to create the index before performing the query?

I am thinking of using

    Create Index MyIndex on mytable (a, b, c, d);

That is, creating index for all a, b, c, d columns. Is that correct?

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Yes, that is the best index for your query. The columns a, b, and c can be in any order, but d needs to follow them.

Upvotes: 3

Related Questions