How can we add an index to the table/type during run time from Java code

[svintha]

Hi , Just wanted to know whether we can add indexes to the table/type during the run time by java code ?

As an example, we have some issue with the cron job which is taking longer time due to some database performance. We just now wanted to add the indexes to the sample addresses table/type just before the flexible search query we are going to execute on this table.

We also wanted to remove the indexes as soon as the flexible search query is run or as soon as the cron job gets completed.

[svintha]

I could only see adding an index to the item type during the item definition in the items.xml file as below

 <indexes>
                 <index name="cmsVersionUniqueIDX" unique="true">
                     <key attribute="uid" />
                 </index>
 </indexes>

But wanted to check wheather we can also add an index during the run time just before we could execute a flexiblesearchquery on the DB Level ,Any suggestions are most welcome .

[former_member624549]

you should not do that even when it is possible. DB indexes are created via indexes statement as you stated. Or when you require a very specific index ask the db admin to create the index for you. With lots of data index creation might take hours. Also your java code creating the index must consider the case where the index is already there or it would throw an exception. Imagine the DB admin optimizes the indexes but your code recreates the index everytime it runs - a maintenance nightmare. Also: are you sure you need an index? DB's are pretty good handling 10 thousands of records just fine without index

[former_member620692]

Hi - In Hybris, all DDL statements should be executed as part of Init/Update. Please also note that Update does not delete any database objects (tables, indexes, columns etc.). You should define the index in items.xml and perform Update.

Nevertheless, you can try the following groovy code to create the database index:

 import de.hybris.platform.core.Registry
 
 conn = Registry.getCurrentTenant().getDataSource().getConnection()
 stmt = conn.createStatement()
 ddlQuery = "YOUR CREATE INDEX SQL COMMAND"
 stmt.executeUpdate(ddlQuery)

[svintha]

Hi , Thanks for the reply. As I heard indexes on the DB are getting wiped out each time we do a build not sure why this occurs. Just wanted to know whether we have any possibility from the Java side to create indexes.

[former_member620692]

I have updated my answer. Please let me know if it serves your use case.

[svintha]

Thanks for the reply . It might not serve the exact requirement but thanks for your time on this. I will try to recommend updating the items.xml file and see the feedback from them.