How to index database columns in Hybris

Indexing Overview


We know that indexing a database column brings up a very good performance while fetching the records from Database especially when data set is huge.

It’s not just in hybris, In any application its good to have indexing on few frequently searching columns of a table.

Whenever we create an index on a column in a table, it creates another data structure which holds the column value , and pointer to the record it relates to.

So, using indexing speeds up the searching for a matching column within the records.

We generally define indexing in items.xml file while defining the item type

We can define indexing using single attribute or multiple attributes based on the requirement.

We can also define multiple indexes for the same item type

Defining indexing with single attribute


When we define indexing for single attribute, we just use one key attribute within index tag.

Below indexing is defined for Region item type

  1.             <indexes>
  2.                 <index name="Region_Country">
  3.                     <key attribute="country" lower="true"/>
  4.                 </index>
  5.             </indexes>
            <indexes>
                <index name="Region_Country">
                    <key attribute="country" lower="true"/>
                </index>
            </indexes>


We need to keep all the indexes for an item type under parent tag

We need to use tag to define the indexing

If we need to define multiple indexes then we need to use “index tag” multiple times within Index tag.

name: specifies the name of the indexing

Key attribute : specifies the attribute qualifier used for indexing

lower = true indicates that elements will be indexed with case-insensitive.

Default is false

We can find below indexing defined while defining Product item type

  1.             <indexes>
  2.                 <index name="Product_Code">
  3.                     <key attribute="code"/>
  4.                 </index>
  5.             </indexes>
            <indexes>
                <index name="Product_Code">
                    <key attribute="code"/>
                </index>
            </indexes>


In this case product code is indexed column

Defining indexing with multiple attributes


We can define indexing using multiple attributes as well

We just need to mention multiple attribute qualifiers using multiple “key attribute” tag as below

  1. <indexes>
  2.                 <index name="codeVersionActiveIDX" unique="true">
  3.                     <key attribute="code"/>
  4.                     <key attribute="version"/>
  5.                     <key attribute="active"/>
  6.                 </index>
  7.             </indexes>
<indexes>
                <index name="codeVersionActiveIDX" unique="true">
                    <key attribute="code"/>
                    <key attribute="version"/>
                    <key attribute="active"/>
                </index>
            </indexes>


In this case, single indexing is created using the combination of 3 attributes code, version and active attributes.

unique = true specfies that the combination of these 3 values should be unique in a table.

We can find this in AbstractDynamicContent item type definition.

Defining Multiple indexes for same item type


In the above 2 cases, we have defined single indexing on an item type.

We can also define multiple indexes on the same item type whenever we have to broader the search columns on that table

In that case, we need to use multiple index tags

Below indexing is defined for Category item type

  1. <indexes>
  2.             <index name="codeIDX" unique="false">
  3.                 <key attribute="code"/>
  4.             </index>
  5.            <index name="versionIDX" unique="false">
  6.                 <key attribute="catalogVersion"/>
  7.             </index>
  8.            <index name="codeVersionIDX" unique="true">
  9.                 <key attribute="code"/>
  10.                 <key attribute="catalogVersion"/>
  11.             </index>
  12. </indexes>
<indexes>
         	<index name="codeIDX" unique="false">
         		<key attribute="code"/>
         	</index>
	       <index name="versionIDX" unique="false">
         		<key attribute="catalogVersion"/>
         	</index>
	       <index name="codeVersionIDX" unique="true">
         		<key attribute="code"/>
         		<key attribute="catalogVersion"/>
         	</index>
</indexes>


We have created 3 indexing columns for Category item type

1) codeIDX indexing defined using code attribute

2) versionIDX indexing defined using catalogVersion attribute

3) codeVersionIDX indexing defined using code and catalogVersion attributes

In this case, 3 different indexing columns will be created in the backend

We can also notice that unique value is given as false for code and version separately, this is because code and Version as an individual attribute can have duplicate values but when we combine them code and catalogVersion combination will always be unique and hence its unique value is given as true

 
Note
1) We should not increase indexing columns unnecessarily as it takes extra memory and costs performance overhead 2) Solr indexing and database column indexing are entirely different concepts Check solr-indexing article for more details on Solr indexing

About the Author

Founder of javainsimpleway.com
I love Java and open source technologies and very much passionate about software development.
I like to share my knowledge with others especially on technology 🙂
I have given all the examples as simple as possible to understand for the beginners.
All the code posted on my blog is developed,compiled and tested in my development environment.
If you find any mistakes or bugs, Please drop an email to kb.knowledge.sharing@gmail.com

Connect with me on Facebook for more updates

Share this article on