Authentic Appian Certification Exam ACD300 Questions Answers

• D. The join between the tables order_detail and product needs to be fine-tuned due to Indices. This is correct because the result of the explain statement showsthat the join between these two tables has a high cost of 0.99, which indicates that it is inefficient and needs to be fine-tuned. One possible reason for the high cost is that there are no indices on the columns that are used for joining these two tables, which leads to a full table scan. Therefore, creating indices on these columns could improve the performance of this join.
• E. The worst join is the one between the table order_detail and customer. This is correct because the result of the explain statement shows that the join between these two tables has a very high cost of 1.00, which indicates that it is the worst join in terms of efficiency and needs to be fine-tuned. One possible reason for the high cost is that there are no indices on the columns that are used for joining these two tables, which leads to a full table scan. Therefore, creating indices on these columns could improve the performance of this join.

The other options are incorrect for the following reasons:

• A. The request is good enough to support a high volume of data, but could demonstrate some limitations if the developer queries information related to the product. This is incorrect because the request is not good enough to support a high volume of data, as it has two joins with very high costs that need to be fine-tuned. Moreover, querying information related to the product would not necessarily cause any limitations, as long as the join between order_detail and product is optimized.
• B. The worst join is the one between the table order_detail and order. This is incorrect because the result of the explain statement shows that the join between these two tables has a low cost of 0.01, which indicates that it is efficient and does not need to be fine-tuned.
• C. The join between the tables order_detail, order and customer needs to be fine-tuned due to indices. This is incorrect because there is no such join between three tables in the result of the explain statement. There are only two joins: one between order_detail and order, and another between order_detail and customer. Each of these joins needs to be fine-tuned separately due to indices.

Indexing columns can improve the performance of queries that use those columns in filters, joins, or order by clauses. In this case, the columns that should be indexed are site_id, status, and priority, because they are used for filtering or joining the tables. Site_id is used to join the case and site tables, so indexing it will speed up the join operation. Status and priority are used to filter the cases by the user’s input, so indexing them will reduce the number of rows that need to be scanned. Name, modified_date, and case_id do not need to be indexed, because they are not used for filtering or joining. Name and modified_date are only used for displaying information in the record grid, and case_id is only used as a unique identifier for each record. Verified References: Appian Records Tutorial, Appian Best Practices

The field would benefit greatly from the index in the following scenarios:

• A. The field contains a textual short Business code. This is a scenario where an index can improve the performance of queries that search for exact matches or ranges of values in the field. A textual short Business code is likely to have high cardinality, meaning that it has many distinct values and low duplication. This makes the index more selective and efficient, as it can quickly narrow down the results based on the search criteria.
• C. The field contains many datetimes, covering a large range. This is a scenario where an index can improve the performance of queries that search for exact matches or ranges of values in the field. A datetime field is likely to have high cardinality, meaning that it has many distinct values and low duplication. This makes the index more selective and efficient, as it can quickly narrow down the results based on the search criteria.
• D. The field contains big integers, above and below 0. This is a scenario where an index can improve the performance of queries that search for exact matches or ranges of values in the field. A big integer field is likely to have high cardinality, meaning that it has many distinct values and low duplication. This makes the index more selective and efficient, as it can quickly narrow down the results based on the search criteria.

The other options are incorrect for the following reasons:

• B. The field contains long unstructured text such as a hash. This is a scenario where an index might not improve the performance of queries that search for exact matches or ranges of values in the field. A long unstructured text field is likely to have low cardinality, meaning that it has few distinct values and high duplication. This makes the index less selective and efficient, as it cannot quickly narrow down the results based on the search criteria. Moreover, indexing a long unstructured text field could increase thestorage space and maintenance cost for the database, which could affect the overall performance.
• E. The field contains a structured JSON. This is a scenario where an index might not improve the performance of queries that search for exact matches or ranges of values in the field. A structured JSON field is not a native data type in MySQL, and it requires special functions or operators to access or manipulate its elements. Indexing a structured JSON field could increase the complexity and overhead for the database, which could affect the overall performance. Verified References: Appian Documentation, section “Query Optimization”.