Re: Programmatic configuration of compound indexes

[vkulichenko <va...@gmail.com>]

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



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Programmatic-configuration-of-compound-indexes-tp1081p1085.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Programmatic configuration of compound indexes

[Alex Rogachevsky <al...@px100systems.com>]

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&lt;Class&lt;?>,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);




--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Programmatic-configuration-of-compound-indexes-tp1081p1099.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.