Is there a way in SQLalchemy to tell the query which index to use?
The reason I need this is that the SQL queries it generates use the "wrong" index - there exists an index for exactly the two fields that I have and it doesn't use it.
Thanks!
SQLAlchemy Index is used for assigning the identifiers for each of the particular row getting stored inside a table. We can have indexing based on the single column or collection of two or more columns together acting as an index to the table rows.
¶ The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well.
Interesting to note that querying using bare sqlite3 is still about 3 times faster than using SQLAlchemy Core.
A foreign key in SQL is a table-level construct that constrains one or more columns in that table to only allow values that are present in a different set of columns, typically but not always located on a different table.
I think you can use with_hint() for this.
e.g.
session.query(Model).with_hint(Model, 'USE INDEX col1_index')
Honestly, I didn't really know about this; I discovered it by finding 'USE INDEX' in their ORM tests.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With