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 <katamaran...@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.
