phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Januszkiewicz <katamaran...@gmail.com>
Subject Re: Indexes not used when ordering by primary key.
Date Fri, 13 Oct 2017 13:47:34 GMT
Small correction the index is local:
CREATE LOCAL INDEX t_c1_ix ON t (c1);

On Fri, Oct 13, 2017 at 3:43 PM, Marcin Januszkiewicz
<katamaran666@gmail.com> wrote:
> Hi,
>
> we have some data in a phoenix table that we always want to fetch in
> the order determined by the primary key:
>
> CREATE TABLE t (
>   rowkey VARCHAR PRIMARY KEY,
>   c1 VARCHAR,
>   c2 VARCHAR,
> )
>
> SELECT rowkey, c1, c2 FROM t where c1 LIKE 'X0%' ORDER BY rowkey;
>
> We wanted to speed up searches using an index on c1:
>
> CREATE INDEX t_c1_ix ON t (c1);
>
> However, this index is only used if we drop the ORDER BY clause or use
> a query hint. If we sort by any other field, such as c2, the index
> will be used.
>
> Is this expected behavior? Is there any way of influencing phoenix to
> use the indexes without using an index hint? The actual table has more
> columns & indexes, and queries are creating programatically. Adding
> code which would decide which hint to generate would be a little
> problematic.

Mime
View raw message