Index Optimization in Database Query Performance: Guidelines and Real-world Examples

Don’t get too smug with your current level after reading a ton of articles (especially, mine) because today, I am going to throw some curveballs at you and if you can hit them all, then you are truly a geek and you deserve a party popper. In the world of database query optimization, the choice of indexes can make or break your queries. This article reveals the secrets of picking and tweaking indexes, supported by real-world examples. We’ll talk about situations where the right index can make your query fly and when it might need a makeover.

Recap on Index Optimization:

And I know you are looking for a cheat sheet, so here is one that could jog your truly brilliant memory when you were diligent. Here’s a crisp recap of key index concepts that can help you nail the questions:

1. Single-Column Indexes: An index on a single column is effective for filtering data based on that column's values. For example, creating an index on a date_column with the current data-- case sensitive,can accelerate queries that involve date-based filtering.

2. Multi-Column Indexes: In scenarios where queries involve filtering by multiple columns or sorting by one column, a multi-column index can be advantageous. It optimizes both filtering and sorting operations. For instance, an index on (a, date_column) works well when filtering by a and sorting by date_column(ordere matters).

3. Multi-Purpose Indexes: Sometimes, a single index can serve multiple query patterns. If you have queries filtering by different combinations of columns, a multi-column index that covers all relevant columns, such as (a, b), can be beneficial for optimizing these queries.

4. Pattern Search Indexes: When performing text pattern searches, consider using specialized index types like varchar_pattern_ops. These indexes are tailored for efficient pattern matching, as seen in the 'text LIKE' query.

5. Impact of Conditions: Introducing additional conditions to a query can affect its performance. Whether it makes the query slower, faster, or keeps it the same depends on the specific data and query. Evaluating these changes is essential for efficient indexing.

for each question you have to choose
1- Good Fit
2- Bad Fit -- could be optimized.

Question1

CREATE INDEX first_name_index ON employee(first_name)

SELECT COUNT(*)
FROM employee 
WHERE UPPER(first_name) = 'GORG';

Question 2

CREATE INDEX name_index ON employee(first_name,last_name)

SELECT first_name, last_name FROM employee
WHERE last_name = 'OoPs'
ORDER BY first_name DESC
FETCH FIRST 2 ROW ONLY

Question 3

CREATE INDEX name_index ON employee(first_name,last_name)

SELECT first_name, last_name FROM employee
WHERE last_name = 'OoPs'
AND first_name = 'Hahah'

SELECT first_name, last_name FROM employee
WHERE last_name = 'OoPs'

Question 4

CREATE INDEX notes_index ON employee(notes text_pattern_ops)

SELECT notes FROM employee 
WHERE notes LIKE 'late%'

Tips

1- Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.
2- Use an index-only scan for queries that access many rows but only a few columns.
Avoid select * to increase chances for an index-only scan.

please, refer to the comment section to see the answer and rate yourself and don't forget to give yourself a Confetti.