Multiple column indexing

Forum|Forum|2 years ago
September 21, 2023
2 replies
1101 views

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?

<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>

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by AkshayAnand

Hi @god_prophet

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

LakshmiPravallika

Community Advisor

Hi @god_prophet ,

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:

So, it all depends on your requirements.

Regards,

Pravallika.

G

god_prophetAuthor

Level 6

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.