CODEWITHSUNDEEP
databasesqliteindexing
Tacitus86
Tacitus86

Reputation: 1404

Database Index vs Primary Key

I want to start off saying I am not a database guru, but I'm decent with the basics.

I have a set of IO data that I'm storing in two tables which are uniquely identified by 'ioid' and 'machinenum'.

I have a 2 tables: IOConfig which uniquely identifies points (all the identifying information and a primary key: ConfigID). And a data table that contains samples of these items.

My table layouts below are to test using a primary key + index versus using just an index, so I know there is duplicate data.

Think of IOConfig table as such:

ConfigId(PK)   machineNum   ioId   ioType

Think of IOData table as such:

Timestamp      ConfigId     machineNum     ioId    value

If I use the ConfigID primary key, with an index on (timestamp,ConfigId) my query is like this:

select * from AnalogInput
where sampleTimestamp>=1520306916007000000 and sampleTimestamp<=1520351489939000000
  and configId in (1112)

"0" "0" "0" "SEARCH TABLE IOData USING INDEX cfgIndexAnalogInput (configId=? AND sampleTimestamp>? AND sampleTimestamp<?)"

If I avoid using ConfigID the query is like this:

select * from AnalogInput
where sampleTimestamp>=1520306916007000000 and sampleTimestamp<=1520351489939000000
and ioId in (1)
and machineid=1111

"0" "0" "0" "SEARCH TABLE IOData USING INDEX tsIndexAnalogInput (sampleTimestamp>? AND sampleTimestamp<?)"

Why wouldn't I get the improvement that I see with the first query + Index of (timestamp,configid) for the second query using an index of (timestamp,machineNum,ioid)? I ask because machineNum and ioid are used to define what point is unique to make a configId primary key... so one would expect them to equate?

schema:

CREATE TABLE 'IOData'(
    'sampleTimestamp' INTEGER,
    'configId' INTEGER,
    'machineId' INTEGER,
    'ioId' INTEGER,
    'value' REAL);

CREATE TABLE 'IOConfig'(
    'sampleTimestamp' INTEGER,
    'configId' INTEGER PRIMARY KEY,
    'machineId' INTEGER,
    'ioId' INTEGER,
    'ioType' INTEGER);

CREATE INDEX `Something` ON `IOData` (`sampleTimestamp` ASC,`machineId` ASC,`ioId` ASC)

CREATE INDEX cfgIndexAnalogInput ON IOData(configId,sampleTimestamp)

CREATE INDEX tsIndexAnalogInput ON IOData(sampleTimestamp)

Upvotes: 1

Views: 1263

Answers (1)

CL.
CL.

Reputation: 180270

Read Query Planning to understand how indexes work, and The SQLite Query Optimizer Overview to see what specific optimization will be applied.

In this case, the filter on sampleTimestamp uses inequality comparisons, so, according to section 1.0, that must be the last column in the index (either in an explicit index, or in a three-column primary key):

CREATE INDEX SomethingBetter ON IOData(machineId, ioId, sampleTimestamp);

Upvotes: 3

Related Questions