samedi 3 octobre 2015

How to use indexes when trying to make null values come last?

I am using sqlite3 and I am trying to retrieve all rows ordered by some col1 with null values coming last. As of now I am using this kind o query:

select * from table order by row1 is null, row1 asc

As there are many rows in my table, the query worked quite slowly, so I decided to create an index on table(row1).

After creating the index it extremely improved the speed of queries like:

select * from table order by row1 asc

However sqlite doesn't seem to use that index with "order by col1 is null" type of queries.

Why sqlite, based on that index, can't just move rows with null values to the end? Is there any way I can make null values come last without the need to evaluate every row every time again?

Aucun commentaire:

Enregistrer un commentaire