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 ?

Solution 1

You can create another index...

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

Solution 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

Comments

[demented hedgehog]

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

[Greg G]

That link to the query planner was very helpful.