CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is troubleshooting a performance bottleneck in a highly transactional online retail database. They observe that queries involving large `JOIN` operations on frequently updated tables are consistently slow. The database is heavily I/O bound. Which optimization technique is MOST likely to yield significant performance improvements in this scenario?
- AReducing the database's `buffer_pool_size` to free up system memory.
- BImplementing row-level security for all sensitive tables.
- CCreating appropriate indexes on the columns used in `JOIN` and `WHERE` clauses.
- DIncreasing the `max_connections` parameter in the database configuration.
Show answer & explanationAnswer & explanation
Correct answer: C. Creating appropriate indexes on the columns used in `JOIN` and `WHERE` clauses.
For I/O-bound queries with large `JOIN` operations, creating appropriate indexes on the columns involved in `JOIN` and `WHERE` clauses significantly reduces the amount of data that needs to be scanned from disk, directly addressing the I/O bottleneck.
Why the other options are wrong
- A. Reducing `buffer_pool_size` would decrease the amount of data cached in memory, leading to more disk I/O and worsening performance.
- B. Row-level security adds overhead and does not directly optimize `JOIN` performance or reduce I/O for existing queries.
- D. Increasing `max_connections` allows more users, potentially worsening performance if I/O is already a bottleneck.
Query Optimization with Indexes
The process of improving query performance by creating and maintaining appropriate indexes on database tables.
- Indexes speed up data retrieval operations (SELECT).
- They are most effective on columns used in WHERE, JOIN, ORDER BY clauses.
- Indexes add overhead to data modification operations (INSERT, UPDATE, DELETE).
Memory trick: Index the joins, speed up the lines.