Programmatic configuration of compound indexes

classic Classic list List threaded Threaded
3 messages Options
Alex Rogachevsky Alex Rogachevsky
Reply | Threaded
Open this post in threaded view
|

Programmatic configuration of compound indexes

This post has NOT been accepted by the mailing list yet.
I configure caches and their indexed/query fields programmatically. Am I doing anything wrong below, as it seems to have no effect on the query? There is no documentation for CacheTypeMetadata.setGroups(), so I am wondering what the boolean in IgniteByTuple means: ascending, descending, something else?

CacheTypeMetadata type = new CacheTypeMetadata();
LinkedHashMap<String, IgniteBiTuplecompoundIndex.put("textField", new IgniteBiTuple<>(String.class, true));
compoundIndex.put("id", new IgniteBiTuple<>(Long.class, false));
type.getGroups().put("compIdx1", compoundIndex);
cacheConfig.setTypeMetadata(Collections.singletonList(type));

The query:
FROM TestEntity WHERE textField LIKE '%Jane%' AND id <= 100000 ORDER BY id DESC LIMIT 100
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: Programmatic configuration of compound indexes

Alex Rogachevsky wrote
I configure caches and their indexed/query fields programmatically. Am I doing anything wrong below, as it seems to have no effect on the query? There is no documentation for CacheTypeMetadata.setGroups(), so I am wondering what the boolean in IgniteByTuple means: ascending, descending, something else?

CacheTypeMetadata type = new CacheTypeMetadata();
LinkedHashMap<String, IgniteBiTuplecompoundIndex.put("textField", new IgniteBiTuple<>(String.class, true));
compoundIndex.put("id", new IgniteBiTuple<>(Long.class, false));
type.getGroups().put("compIdx1", compoundIndex);
cacheConfig.setTypeMetadata(Collections.singletonList(type));

The query:
FROM TestEntity WHERE textField LIKE '%Jane%' AND id <= 100000 ORDER BY id DESC LIMIT 100
Alex,

"textField LIKE '%Jane%'" condition can't use index because there is a wildcard in the beginning. For this query it's better to have an individual index on 'id' field. This way Ignite will be able to filter the result set using this index and therefore reduce the number of scanned rows.

BTW, we recently updated docs for SQL queries: https://apacheignite.readme.io/v1.3/docs/sql-queries. It has information about group indexes and some performance tips.

-Val
Alex Rogachevsky Alex Rogachevsky
Reply | Threaded
Open this post in threaded view
|

Re: Programmatic configuration of compound indexes

That probably wasn't the best example. I took it from my mini-benchmark on StackOverflow. Here's more (see below). The question is theoretical at this point - to verify that programmatic configuration of compound indexes works. You can suggest a better example, and in any case I'd like to see that API:  CacheTypeMetadata.setGroups() explained in both the documentation and javadoc.

A new Ignite user would greatly benefit from a query tutorial, when you describe the entity (java bean) first, then perform some queries against its fields on a large data set, and show how long it takes. Then introduce single indexes and show queries that hit them and those that miss the index, explaining why it happens. Then do the same with a compound index. You should show all three examples of configuration: annotation-based, XML, and programmatic (Java API with those maps of maps of bi-tuples). Am I the only one using that particular less than user-friendly method signature?

Here is my example regardless:
http://stackoverflow.com/questions/31932836/hazelcast-vs-ignite-benchmark/31933051#31933051
https://github.com/a-rog/px100data/tree/master/examples/HazelcastVsIgnite

CacheConfiguration<Long, TestEntity> cacheConfig = new CacheConfiguration<Long, TestEntity>();
                cacheConfig.setName(ENTITY_NAME).
                        setCacheMode(CacheMode.PARTITIONED).
                        setBackups(config.getAtomicConfiguration().getBackups()).
                        setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL).
                        setWriteBehindEnabled(false).
                        setWriteSynchronizationMode(CacheWriteSynchronizationMode.FULL_SYNC).
                        setReadThrough(false);

                CacheTypeMetadata type = new CacheTypeMetadata();
                type.setValueType(TestEntity.class.getName());

                type.getQueryFields().put("id", Long.class);
                type.getAscendingFields().put("id", Long.class);

                type.getQueryFields().put("createdAt", Date.class);
                type.getAscendingFields().put("createdAt", Date.class);

                type.getQueryFields().put("modifiedAt", Date.class);
                type.getAscendingFields().put("modifiedAt", Date.class);

                type.getQueryFields().put("textField", String.class);
                type.getAscendingFields().put("textField", String.class);

                LinkedHashMap<String, IgniteBiTuple<Class<?>,Boolean>> compoundIndex = new LinkedHashMap<>();
                compoundIndex.put("textField", new IgniteBiTuple<>(String.class, true));
                compoundIndex.put("id", new IgniteBiTuple<>(Long.class, false));
                type.getGroups().put("compIdx1", compoundIndex);

                compoundIndex = new LinkedHashMap<>();
                compoundIndex.put("id", new IgniteBiTuple<>(Long.class, false));
                type.getGroups().put("compIdx2", compoundIndex);

                compoundIndex = new LinkedHashMap<>();
                compoundIndex.put("textField", new IgniteBiTuple<>(String.class, false));
                compoundIndex.put("id", new IgniteBiTuple<>(Long.class, false));
                type.getGroups().put("compIdx3", compoundIndex);

                cacheConfig.setTypeMetadata(Collections.singletonList(type));
                ignite.createCache(cacheConfig);

...

SqlQuery<Object, TestEntity> query = new SqlQuery<>(TestEntity.class,
                        "FROM TestEntity WHERE textField LIKE '%Jane%' AND id > '" + first.getId() + "' ORDER BY id LIMIT 100");

...

query = new SqlQuery<>(TestEntity.class,
                        "FROM TestEntity WHERE textField LIKE '%Jane%' AND id <= '" + last.getId() + "' ORDER BY id DESC LIMIT 100");

...

query = new SqlQuery<>(TestEntity.class,
                        "FROM TestEntity WHERE textField LIKE '%Richards%' AND id > '" + first.getId() + "' ORDER BY id ASC LIMIT 100");

...

query = new SqlQuery<>(TestEntity.class,
                        "FROM TestEntity WHERE textField LIKE '%Richards%' AND id <= '" + last.getId() + "' ORDER BY id DESC LIMIT 100");
                query.setPageSize(100);