Use of Index with order by

[Nikhil Chandrappa <nc...@pivotal.io>]

Hi All,

We have a region with 3 million records and we have put a index on the
field which we are using for order by. From the <trace> we see that index
is not being used, could you please let us know why index will not be used.

Following is the query

"select distinct * from /UFCH order by read_datetime desc limit 10;"

Range Index Created

"create index --name=myIndex --region=/UFCH --expression=read_datetime"

Index has no effect on the query execution time when used with order by.

-- 

*Nikhil Chandrappa *| Data Engineer | New York

(315) 396 - 3789 | nchandrappa@pivotal.io | Pivotal Software Inc.
<http://www.pivotal.io/>

Re: Use of Index with order by

[Tommy Jeppesen <tj...@pivotal.io>]

Hi Nikhil,

You are selecting all entries with SELECT * so there is no need for the
index. You need to restrict the query with a WHERE clause on the field that
the index is created on for the index to be used, as Udo pointed out.



Best regards,



Tommy Jeppesen

Data Fabric Technical Support Engineer

EMEA - Spain

Email: tjeppesen@pivotal.io

Mobile: +34 646 878 424

On Wed, Dec 16, 2015 at 9:57 PM, Udo Kohlmeyer <uk...@pivotal.io>
wrote:

> Hi Nikhil,
>
> From my basic querying and database knowledge,  I can only guess that the
> reason why the index is not used is because you are not restricting on the
> field you have an index on.
>
> I don't think that ordering would use indexes. Indexes are primarily there
> to efficiently find objects not to sort them.
>
> --Udo
>

Re: Use of Index with order by

[Udo Kohlmeyer <uk...@pivotal.io>]

Hi Nikhil,

>From my basic querying and database knowledge,  I can only guess that the
reason why the index is not used is because you are not restricting on the
field you have an index on.

I don't think that ordering would use indexes. Indexes are primarily there
to efficiently find objects not to sort them.

--Udo