SQL Query index type

classic Classic list List threaded Threaded
2 messages Options
JohnnyS JohnnyS
Reply | Threaded
Open this post in threaded view
|

SQL Query index type

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
Sergi Vladykin Sergi Vladykin
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query index type

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 <[hidden email]>:
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.