Google Professional Cloud Database Engineer Question 117
Select 2Google Cloud PlatformYou are managing a Cloud SQL database for a retail application. Users report a significant slowdown in query performance when retrieving order details. Upon investigation, you observe that the same query is being executed frequently and causing database locking issues. How can you address the slow query and reduce database locking?
- A
Use the Query Insights feature in Cloud SQL to identify missing indexes and optimize the query.
- B
Enable read replicas for the database and direct read queries to the replicas.
- C
Add an index on the columns frequently used in WHERE clauses for the slow query.
- D
Increase the number of CPUs and memory allocated to the database instance.
- E
Rewrite the query to use SELECT * to retrieve all columns from the table.
Show answer and explanation
Correct answers: A, C
Explanation
When dealing with slow queries and database locking, it is important to address the root cause, such as missing indexes and inefficient query design. Using Query Insights allows you to analyze query performance and identify missing indexes, while adding indexes on frequently queried columns improves efficiency. While other options like increasing resources or enabling read replicas can provide temporary relief, they do not resolve the underlying issues causing the performance degradation.
- A. Correct.
Query Insights is a powerful tool in Cloud SQL that helps identify slow queries and missing indexes. By using this feature, you can pinpoint performance bottlenecks and optimize queries effectively.
- B. Incorrect.
Enabling read replicas can help distribute read traffic, but it does not directly address performance issues caused by missing indexes or lock contention in the primary database.
- C. Correct.
Adding an index on frequently queried columns can significantly improve query performance and reduce locking by allowing the database to retrieve data more efficiently.
- D. Incorrect.
Increasing the database instance's resources may temporarily improve performance but does not resolve the root cause of the slow query or locking issues.
- E. Incorrect.
Using SELECT * retrieves unnecessary data and can increase query execution time and locking, making the issue worse.