What index should I create ?

classic Classic list List threaded Threaded
4 messages Options
tomk tomk
Reply | Threaded
Open this post in threaded view
|

What index should I create ?

Hello,
My question is:
What index(es) should I create ? I have 10 columns, where four of  them will be used as filter (where - equality and inequality) and field for groupby.  
It should be four separate index, as:
                               <constructor-arg value="A"/>
                                                <constructor-arg value="B"/>
                                                <constructor-arg value="C"/>
                                                <constructor-arg value="D"/>
or group index:
                                              <constructor-arg>
                                                  <list>
                                                      <value>A</value>
                                                      <value>B</value>
                                                      <value>C</value>
                                                      <value>D/value>                                                      
                                                  </list>
                                              </constructor-arg>
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: What index should I create ?

Hi,

It depends on the query that you're going to execute, size of the table and sizes of the result sets. Please provide this information and I will try to help.

-Val
tomk tomk
Reply | Threaded
Open this post in threaded view
|

Re: What index should I create ?

This post was updated on .
1. WHERE {A,B,C,D}
(all possible combinations - AND, for example)
where A > 100 AND D < 1000
WHERE B > 20
....

GROUPBY(A)
GROUPBY(B)
GROUPBY(C)
GROUPBY(D)

These indexes I should set in xml file for server.  SHould I set it in client xml file (JDBC client)
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: What index should I create ?

Please refer to [1] and other sections of 'SQL queries' page for some information about this. Most likely, in your case you will need to create individual indexes for all field that can be used in WHERE clause. Whether or not you need group indexes, depend on subset sizings after indexes are applied and on particular queries you're going to execute. You should run tests and use EXPLAIN to check indexes are used.

[1] https://apacheignite.readme.io/docs/sql-queries#choosing-indexes

-Val