AWS Certified Database – Specialty — Question 315

A manufacturing company has an. inventory system that stores information in an Amazon Aurora MySQL DB cluster. The database tables are partitioned. The database size has grown to 3 TB. Users run one-time queries by using a SQL client. Queries that use an equijoin to join large tables are taking a long time to run.
Which action will improve query performance with the LEAST operational effort?

Answer options

• A. Migrate the database to a new Amazon Redshift data warehouse.
• B. Enable hash joins on the database by setting the variable optimizer_switch to hash_join=on.
• C. Take a snapshot of the DB cluster. Create a new DB instance by using the snapshot, and enable parallel query mode.
• D. Add an Aurora read replica.

Correct answer: B

Explanation

Enabling hash joins in Amazon Aurora MySQL significantly improves the performance of equijoins on large datasets by using a hash join instead of a nested loop join, requiring only a simple configuration change. Migrating to Amazon Redshift or restoring a snapshot to enable parallel query requires substantial operational effort and downtime. Adding a read replica offloads query volume but does not inherently optimize the execution plan of the slow-running equijoin queries.