CODEWITHSUNDEEP
indexingsqlite
Guilherme Salome
Guilherme Salome

Reputation: 2039

Creating an Explicit Index column out of an Index in SQLite

I have a very big SQLite3 database that looks like this:

enter image description here

I created an Index out of the 4 columns displayed above:
QUOTE_DATE, EXPIRATION, STRIKE, OPTION_TYPE.
To do so, I ran CREATE INDEX DEST_INDEX ON DATA(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE);.

I was under the impression that the index would be added as a new column in my table. And that I would be able to loop through my database using this index in a for loop. I was wrong.

Problem:
I want to be able to loop through my database (outside SQL, in Matlab, for example) by making queries like SELECT * FROM DATA WHERE INDEX=i. But the index that I created is not explicitly available.

Question:
Is it possible to create an explicit index as a column that mimics the one I created with CREATE INDEX?

Is it possible to make queries using the index, but without using WHERE for each different column? Is there a better solution?

Thanks for helping!

Upvotes: 1

Views: 293

Answers (1)

Yunnosch
Yunnosch

Reputation: 26703

Based on the fact that you will not have to extend/update the new table, you can simply skip doing a trigger and do not have to worry about changing whatever inserts the data.

So you can just create a new version of your table, "idata".
Then check the result and, if you like it, drop the old table and rename the new one.
(I do not provide code for that, because I do not want to be the author of the code which killed your database.;-) It seems ancient and is probably not easy to replace.)

I made my own MCVE for your problem, it is at the end of the answer.
Please consider providing one for your next SQLite question.

Note:
You might want to only use the table "idxdata". It consists of only the non-redundant entries in "data". That would save a lot of space, especially if "data" is really as big as you say.
In that case you should remove the "temporary" keyword. And not use the code from create table idata at all.

create temporary table idxdata
( QUOTE_DATE date, 
  EXPIRATION date, 
  STRIKE int, 
  OPTION_TYPE CHAR(1), 
  idx integer primary key
);

insert into idxdata 
  select distinct
    QUOTE_DATE,
    EXPIRATION,
    STRIKE,
    OPTION_TYPE,
    NULL
  from data;

Content of "idxdata":

2012-01-03|2012-01-06|1000|C|1
2012-01-04|2012-01-07|1000|C|2
2012-01-04|2012-01-07|1000|B|3
2012-02-04|2012-02-07|1000|B|4

More code for extending data to idata, with additional idx column):

create table idata
( QUOTE_DATE date,
  EXPIRATION date,
  STRIKE int,
  OPTION_TYPE CHAR(1),
  idx integer);

insert into idata
  select *
  from data
  join idxdata
  using (QUOTE_DATE, EXPIRATION, STRIKE, OPTION_TYPE);

select * from idata;

OUTPUT:

QUOTE_DATE  EXPIRATION  STRIKE      OPTION_TYPE  idx
----------  ----------  ----------  -----------  ----------
2012-01-03  2012-01-06  1000        C            1
2012-01-03  2012-01-06  1000        C            1
2012-01-03  2012-01-06  1000        C            1
2012-01-03  2012-01-06  1000        C            1
2012-01-04  2012-01-07  1000        C            2
2012-01-04  2012-01-07  1000        C            2
2012-01-04  2012-01-07  1000        C            2
2012-01-04  2012-01-07  1000        B            3
2012-01-04  2012-01-07  1000        B            3
2012-02-04  2012-02-07  1000        B            4
2012-02-04  2012-02-07  1000        B            4
2012-02-04  2012-02-07  1000        B            4

MCVE foundation:

CREATE TABLE data(QUOTE_DATE date, EXPIRATION date, STRIKE int, OPTION_TYPE CHAR(1));
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-03','2012-01-06',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-03','2012-01-06',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-03','2012-01-06',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-03','2012-01-06',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-04','2012-01-07',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-04','2012-01-07',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-04','2012-01-07',1000,'C');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-04','2012-01-07',1000,'B');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-01-04','2012-01-07',1000,'B');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-02-04','2012-02-07',1000,'B');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-02-04','2012-02-07',1000,'B');
INSERT INTO data(QUOTE_DATE,EXPIRATION,STRIKE,OPTION_TYPE) VALUES('2012-02-04','2012-02-07',1000,'B');

Using: SQLite 3.18.0 2017-03-28

Upvotes: 1

Related Questions