sqlite3 index table in python

Question

I have created this table in python 2.7 . I use it to store unique pairs name and value. In some queries I search for names and in others I search for values. Lets say that SELECT queries are 50-50. Is there any way to create a table that will be double index (one index on names and another for values) so my program will seek faster the data ?

Here is the database and table creation:

import sqlite3
#-------------------------db creation ---------------------------------------#
db1 = sqlite3.connect('/my_db.db')
cursor = db1.cursor()

cursor.execute("DROP TABLE IF EXISTS my_table")

sql = '''CREATE TABLE my_table (
        name TEXT DEFAULT NULL,
        value INT
        );'''
cursor.execute(sql)

sql = ("CREATE INDEX index_my_table ON my_table (name);")
cursor.execute(sql)

Or is there any other faster struct for faster value seek ?

woot · Accepted Answer · 2014-05-18 16:03:46Z

9

You can create another index...

sql = ("CREATE INDEX index_my_table2 ON my_table (value);")
cursor.execute(sql)

edited May 18, 2014 at 16:03

answered May 17, 2014 at 20:22

woot

7,6263 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vimuth · Accepted Answer · 2022-07-10 22:40:36Z

2

I think the best way for faster research is to create a index on the 2 fields. like: sql = ("CREATE INDEX index_my_table ON my_table (Field1, field2)")

Multi-Column Indices or Covering Indices. see the (great) doc here: https://www.sqlite.org/queryplanner.html

edited Jul 10, 2022 at 22:40

vimuth

5,87355 gold badges98 silver badges128 bronze badges

answered Aug 27, 2018 at 18:07

j666

3491 gold badge2 silver badges9 bronze badges

1 Comment

demented hedgehog Over a year ago

This answer isn't relevent to the question above. It does answer my question however.

Collectives™ on Stack Overflow

sqlite3 index table in python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related