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.

Comments

[NightBird07]

2- second question: The trick is that the index helps the database to filter and sort the rows at the same time. The database uses the index to locate the most recent row that meets the filter condition and returns it as the result. There is no need to sort any rows, even though there is an order by clause.
so the ans is Good Fit

[rubengura]

But if the index is based on first_name and last_name, and the where clause only filters based on the last_name, shouldn't this affect negatively the performance? 🤔

[NightBird07]

you are absolutely right, how would I improve the performance of such a query ?

[NightBird07]

1- first question this question is used to measure your understanding of how actually index works how they are stored and how they are built. though we have built an index on the column but still not working. An index whose definition contains functions or expressions is a so-called function-based index (FBI). Instead of copying the column data directly into the index, a function-based index applies the function first and puts the result into the index. As a result, the index stores the names in all caps notation.
so the short answer is bad fit

[NightBird07]

Last Question: This index can work well with like expressions that end with a wild card character (% or _), but not with those that start with one. The index can also help with patterns that have the wild card character in the middle.
Good Fit
if you like it share it with your monkeys!

[NightBird07]

3- third question: 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.
Bad Fit