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
- <indexes>
- <index name="Region_Country">
- <key attribute="country" lower="true"/>
- </index>
- </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
We need to use
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
- <indexes>
- <index name="Product_Code">
- <key attribute="code"/>
- </index>
- </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
- <indexes>
- <index name="codeVersionActiveIDX" unique="true">
- <key attribute="code"/>
- <key attribute="version"/>
- <key attribute="active"/>
- </index>
- </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
- <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>
<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
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
Note
IF you want to have some cover index with included Columns, now you can https://help.sap.com/docs/SAP_COMMERCE/d0224eca81e249cb821f2cdf45a82ace/8bffa9cc86691014bb70ac2d012708bc.html?version=2105#defining-index-with-included-columns-in-items.xml-for-microsoft-sql-server
if we have index an attribute in parent item, shall we index same attribute in child items
Hi KP,
Solr indexing has nothing to do with the underlying database / hybris backend you are using.
Hi Nitin,
Same has been written in the Note section at the end of this article, Please read that.
HI KB,
How is the solr indexing and database column indexing are different,Can you explain? and where this indexing will be helpful and can you paint that also?
….