ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergi Vladykin <sergi.vlady...@gmail.com>
Subject Re: SQL Query index type
Date Tue, 11 Aug 2015 00:44:28 GMT
Hi!

Currently Ignite does not support hash indexes.

As for order of fields in grouped index, let me explain.

First thing to notice is that having index on a single field does not
consume
less resources than index on a multiple fields.
The second thing: if you have an index on fields (a, b)
then it does not make any sense to have additional index on (a), because
you don't need a condition on `b` to use (a, b) index.
Thats why grouped indexes are preferable over single field indexes.

But if you have index on (a, b) and query has condition only for `b`, then
it is
impossible to use this index, you need to have a separate one for `b`, or
grouped index starting with `b` like (b, c, a).

The problem here is that each additional index needs additional time
to be updated on each cache modification, as well as additional memory. Thus
creating index for every fields combination probably isn't that good idea.

Thats why having good set of grouped indexes is a key for a good
performance.

Ok, how can we reason about these indexes and how SQL engine will use them?

Index is simply a data structure sorted by fields in a given order. Thus it
is possible
to take any range if you have bounds. Lets say we have an index on (a,b,c)
and thus rows are sorted first by field `a`, then inside of each group with
the same
`a` they sorted by `b`, then the same way by `c`. Thats why it is
impossible to use this
index when we have condition for `b` but not for `a`. Also if we have a
conditions
for `a` and `c` but not for `b`, then index can be used but only for
condition on `a`.

Another thing is ORDER BY optimization. If you already have index on (a, b)
and have `ORDER BY a, b` clause in a query, then there is no need to sort
at all.
It is obvious that it will not work if you have an index on (b, a).
The same is applicable to GROUP BY clause.
Currently this optimization is not implemented in Ignite but it will be
done soon.

Sergi

2015-08-10 13:15 GMT+03:00 JohnnyS <jtksai@gmail.com>:

> Hi
>
> I'm using Apache Ignite to query a large spatial database using a z-order
> curve and I'm trying to improve the query performance. One way to do this
> is
> to use a hash index for equal queries. The H2 documentation says
> (http://www.h2database.com/html/grammar.html#create_index) that "Hash
> indexes are meant for in-memory databases and memory tables (CREATE MEMORY
> TABLE). For other tables, or if the index contains multiple columns, the
> HASH keyword is ignored. Hash indexes can only test for equality, and do
> not
> support range queries (similar to a hash table)."
>
> Does the ignite indexing system support hash indices and is there a way to
> force the index type?
>
> On a somewhat related note, could you clarify the order parameter in the
> QuerySqlField.Group. If I have several indexed fields in a group and set
> different orders for the indices, how does this affect the query behaviour
> and performance?
>
> Thanks,
> johnny
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/SQL-Query-index-type-tp886.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>

Mime
View raw message