2👍
The trouble with sqlite and mysql is that they can use only one index per table as mentioned at https://www.sqlite.org/optoverview.html
Each table in the FROM clause of a query can use at most one index
(except when the OR-clause optimization comes into play) and SQLite
strives to use at least one index on each table
And it get’s worse because sqlite query parser transforms the ON condition to a WHERE clause. Even without the IS NULL your WHERE clause is rather heavy. And it get’s worse because you have an order by.
SQLite attempts to use an index to satisfy the ORDER BY clause of a
query when possible. When faced with the choice of using an index to
satisfy WHERE clause constraints or satisfying an ORDER BY clause,
SQLite does the same cost analysis described above and chooses the
index that it believes will result in the fastest answer.
In many situations mysql can use another index for the order by but sqlite cannot. Postgresql, arguably the best open source RDBMS can use multiple indexes on each table.
So in short there is no way that sqlite can use an index for your IS NULL comparison. using EXPLAIN on the query would reveal that the available index is used on fk_ip_id
Edit:
I am not as proficient on sqlite explain output as I am on postgresql or mysql, but from what I understand of it shows that each table uses one index as discussed above. The data_manager_m_ip table is the one that makes the best use of indexes. There the table itself isn’t even looked at all data is retrieved from the index itself.
The explain also does show that the index on fk_query_id is used. However my understanding is that this is used for the join. The explain also shows that there isn’t any usage of indexes for the sorting. Can you post the explain for the other query as well.
Edit 2:
There you are, it’s hazardous to optimize without looking at EXPLAIN. We were guessing at it was the is null comparison that’s slow. but it’s not!! When you do the IS NULL comparison, sqlite uses the index for that but the IN clause is now without an index and that makes it horribly slow!!
Solution: You need a composite index for fk_query_id, fk_ip_id you can use django index_together to make one.