How to add index to optimise SQLite query?-CodePudding

I'm using a Sqlite database for my Python application.

In that database there's a huge table (about 5M records) and some queries are too slow.

Here is the table:

CREATE TABLE mytable(
ID integer PRIMARY KEY,
Code text,
Value1 float,
Value2 float,
Date text);

and an example of slow query:

SELECT MIN(ID) FROM mytable WHERE Date >= datetime('2022-05-25');

How can I optimise the table to perform the previous query faster? Since the Date column have the same order of the ID column, can I add an index on the Date column?

CodePudding user response：

You most definitely can add an index to the datetime column. Based on your query it would certainly help.

So in the end, you would be looking at something like this:

Create Index myIndex On myTable(Date);

You can find more info regarding Sqlite indexes here: https://www.tutorialspoint.com/sqlite/sqlite_indexes.htm