Multiple column indexing

[ogonzalesdiaz]

Hi, I'm doing indexing of 2 columns like this: users will look up the "codinternocomputacional" and then the "CODCANALCOMUNICACIONCLI". 

Is this the best way to do it for a faster query?

<!--Cardinalidad Recipient vs. PREFERNCIACOMUNICACION 1-N -->
<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revIntegrity="normal" revLabel="Recipient PreferenciasCom Link" revLink="MD_PREFERENCIACOMUNICACION_CCM"
target="nms:recipient" type="link">
<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

<!--Indices -->
<dbindex name="codinternocomputacional">
     <keyfield xpath="@codinternocomputacional"/>
</dbindex>

<dbindex name="CODCANALCOMUNICACIONCLI">
    <keyfield xpath="@CODCANALCOMUNICACIONCLI"/>
</dbindex>

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

The index setup will depend on your requirements.

In your case, you have defined 2 indexes having a column each ie, @codinternocomputacional,@CODCANALCOMUNICACIONCLI"

It will be useful when you fetch the data on each of the columns in a query, the data fetching will be faster because of using an index.

for Example, if you want the data fetching faster on combination of both the columns used in query, then you can create the index as shown below:

<dbindex name="CODCANALCOMU_CombinedIndex">

   <keyfield xpath="@codinternocomputacional"/>
    <keyfield xpath="@CODCANALCOMUNICACIONCLI"/>
</dbindex>

So, it all depends on your requirements.

Regards,

Pravallika.

[ogonzalesdiaz]

Hi Lashmi, what about if I want to have both obtions: filter columns individually or both at the same time? 

Should I add this to the schema? Additionally of what I currently have.

<dbindex name="CODCANALCOMU_CombinedIndex">

   <keyfield xpath="@codinternocomputacional"/>
    <keyfield xpath="@CODCANALCOMUNICACIONCLI"/>
</dbindex>

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

Yes, if you have both the use cases, it would be better to add the combined index also, 

But Please make sure that your Data Insertions into this schema are not getting slower because of this change.(If possible do one round of testing).

Regards,

Pravallika.

[AkshayAnand · Accepted solution]

Hi @ogonzalesdiaz 

You can use the index which you have created alone for better sorting and search criteria. Clustering of the table is done on one field only (mostly PKs.) and once the clustering is done the search is performed quickly.  For reference.

Also having too many unnecessary indexing decreasing few SQL operations and makes the table heavy as indexes utilises disk space. That is the reason, the person who created the schema had removed auto-generated dbindex in 'Recipient cardinality' by adding noDbIndex="true" in element tag.

For Reference.

I appreciate @LakshmiPravallika approach and response but in my thoughts I would suggest to have the indexes which you already have created in the schema.

Regards

Akshay

[Parvesh_Parmar]

Hello @ogonzalesdiaz , 

It is also explained on the below documentation how index works.

Data model best practices | Adobe Campaign

Hope it will be useful. 

Kr, 

Parvesh