Microsoft Dynamics AX (Archived)

Index -field used to join tables

(0) Share

Report

Posted on by RDivya

200

Hi,

Is it required to add the field used in joining tables as part of index.

select * from TableA

join * from TableB

where TableA.field1 == TableB.field2

&& TableA.field3 == 20;

Should I add field1 as part of index of TableA?

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (4)

Blue Wang on at

Like (0)

Report

Hi Divya,

You can add it.

You can get inspiration from AOT.

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,166 Moderator on at

Like (0)

Report

Fields that are used for searching or filtering on the table (including joins) should be covered by an index. Otherwise your queries will not perform well, because the database has to search through all the records to find your results.

Was this reply helpful? Yes No
RDivya 200 on at

Like (0)

Report

Hi Nikolaos/BlueWang,

Apex SQL plan did not suggest to add the field used in joining tables (in Missing index for tableA). Is it because the field is available in the index of TableB ? Kindly suggest. Thanks.

Was this reply helpful? Yes No
Blue Wang on at

Like (0)

Report

Hi Divya,

Relations can be used in two tables by creating only one table.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Relations defined in AX plays no role in how the database server fetches data.

Which index is needed depends on the execution plan. For instance, if the database server first filters tableA by field3 and then takes TableA.field1 and tries to find it in TableB.field2, you would need an index for TableB.field2. But if tableB has just a few small records, making a table scan may be faster anyway.

There is simply not a single universal answer. SQL is a declarative language and the query optimizer may choose different ways how to fetch the data you asked for.

Was this reply helpful? Yes No
RDivya 200 on at

Like (0)

Report

Thanks Martin.

Table A- since field1 is not filtered by any specific value in where clause similar to field3, field1 is not specified in missing index of execution plan.

Is my understanding correct.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

No, that's not what I'm saying.

You statement suggests that indexes are needed just for WHERE clauses (and not joins) and contradicts my point that what indexes are needed depends on how the query is executed by the database server.

Was this reply helpful? Yes No
RDivya 200 on at

Like (0)

Report

Thanks Martin.

Can I go with the suggestion given in missing index to create index (which does not include all fields specified in where clause). Or can I create an index based on all the fields in where clause.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Both approaches are possible - and these are not only ones (e.g. you can create an index with include columns or it may be more efficient not to create any index at all, if its maintenance would overweight the benefits).

I can't say what would be the best in your case, because I don't know how TableA and TableB are used.

Was this reply helpful? Yes No