CODEWITHSUNDEEP
c++sqlsqlite
Hani Goc
Hani Goc

Reputation: 2441

Slow Selection Query even after indexing the table (sqlite and c++)

Create tables

I have a database composed of two tables:

  1. ENTITE_CANDIDATE
  2. VARIATIONS

Tables are created by using the following queries:

CREATE TABLE IF NOT EXISTS ENTITE_CANDIDATE (ID INTEGER PRIMARY KEY NOT NULL, ID_KBP TEXT NOT NULL, wiki_title TEXT, type TEXT NOT NULL);"

CREATE TABLE IF NOT EXISTS VARIATIONS (ID INTEGER PRIMARY KEY NOT NULL, ID_ENTITE INTEGER, NAME TEXT, TYPE TEXT, LANGUAGE TEXT, FOREIGN KEY(ID_ENTITE) REFERENCES ENTITE_CANDIDATE(ID));"

Index tables

I indexed the previous tables by using the following queries:

`CREATE INDEX var_id ON VARIATIONS (ID, ID_ENTITE, NAME);`
`CREATE INDEX entity_id ON ENTITE_CANDIDATE (ID, wiki_title);`

Retrieve information

I want to retrieve from table VARIATIONS the following records:

"SELECT  ID, ID_ENTITE, NAME FROM VARIATIONS WHERE NAME=foo ;"

Every select query is taking around 5.414931 seconds. I know the table contains a very large number of records. But can I make the retrieval faster? Am I indexing correctly the tables?

Upvotes: 0

Views: 270

Answers (1)

CL.
CL.

Reputation: 180080

The documentation says:

the index might be used if the initial columns of the index … appear in WHERE clause terms.

This query uses only the NAME column to search, so the var_id index cannot be used. (That index is useful only for lookups that use ID, which is mostly useless because the ID column is already indexed as PRIMARY KEY.)

Upvotes: 1

Related Questions