Inefficient query time with ORDERY BY clause, are there any alternatives?

[Baller4lifeII]

Hey there,

I've been chasing a problem for the project that I'm working on and was able to narrow it down to the ORDER BY clause in my query.

The algorithm looks like this, I take a range of time selected by the user and I need to do some calculations on the items within that range of time. However, before I calculate, I need to query for the most recent row of a specified item before that time range. So my Query statement ends up looking something like this:

SELECT * FROM table WHERE ....... ORDER BY date_time DESC LIMIT 1.

I'm pretty sure the problem is the ORDER BY clause orders the whole db before doing my WHERE clauses and the LIMIT thus it takes an extremely long time when my db is decently large. This is not acceptable and renders the LIMIT clause almost useless with respect to saving time.

If anyone knows of a way to get around this that can boost my execution time, please let me know! Thanks.

[bharad]

Creating an index on date_time attribute would help.

CREATE INDEX IDX_table_date_time ON table (date_time);

-bharad

Hey there,

I've been chasing a problem for the project that I'm working on and was able to narrow it down to the ORDER BY clause in my query.

The algorithm looks like this, I take a range of time selected by the user and I need to do some calculations on the items within that range of time. However, before I calculate, I need to query for the most recent row of a specified item before that time range. So my Query statement ends up looking something like this:

SELECT * FROM table WHERE ....... ORDER BY date_time DESC LIMIT 1.

I'm pretty sure the problem is the ORDER BY clause orders the whole db before doing my WHERE clauses and the LIMIT thus it takes an extremely long time when my db is decently large. This is not acceptable and renders the LIMIT clause almost useless with respect to saving time.

If anyone knows of a way to get around this that can boost my execution time, please let me know! Thanks.

[Baller4lifeII]

Hey bharad, thanks for the post. I've looked into this matter for a while and all possible solutions point to creating an Index. So I have a few standard questions about db index (keep in mind I am using SQLite).

If I create an Index on date_time, would it modify the date_time column to exclude non-unique elements? Because I need to store multiple items with the same date stamp, does that effect the index?

On 2nd note, is there any way to check whether or not an index has already been created for the specified column? There's no point in creating an index for a column that already has one.

Thanks.

Creating an index on date_time attribute would help.

CREATE INDEX IDX_table_date_time ON table (date_time);

-bharad