How can you optimize your database indexes for faster search?

The database engines offer a way to inform if a WHERE clause had a poor performance and you should create an index to speed it up. In Oracle, it's the SQL Tuning Advisor. When the cardinality (estimated rows that match the condition) doesn't match the number of rows processed by a FULL SCAN or a RANGE SCAN INDEX of other index used, Oracle offers the recommendation of the exact index needed with 99% of accuracy. If we needed only 5 rows that matched the condition, and we retreived 1 million (by full scan or searching for another column and filtering), it's clear than that index for those column conditions will be benefitial. Don't re-invent the wheel, use the performance tools of your database engine (as much as you have them licensed).

As a Database Administrator you know that single-column indexes work well for databases with tables containing a large number of rows and where queries frequently filter or sort data based on a single column. For instance, if you are regularly looking up users by their usernames, consider creating an index for the "username" column in the user table for faster retrieval.

Although we want our systems to deliver high performance, we must be prudent in our designs of the indexes in our databases. So, we should not build an index on each and every column of a table, nor should we have indexes that contain too many columns. The design and choice of the clustered index is of paramount importance for a high-performance system. Also, appropriately-designed indexes are required for primary keys and foreign keys.

Fragmentation is one of the most under looked aspect in performance. A highly transactional environment is bound to have some fragmentation. DBA’s must have a routine check and a a schedule to reorg the tables and associated indexes. This will help maintain some consistency in performance. Ideally once a quarter would be helpful but it depends upon the size of the databases.

you need to keep in mind rebuilding indexes can result increased space usage because a percentage of space on each leaf level left empty providing space for index expansion.

Understand the basics. Even SQL is a declarative language (you don't tell HOW, but WHAT), you have to understand that sometimes the code declares something different from our intentions. When you match a string with a number and you are falling under implicit conversions incompatible with index usage, or mix TIMESTAMP datatypes with DATE datatypes. You are confusing the optimizer and probably get a bad performance in the future. Use analytic functions when it's proper to do it, and avoid subquering groups unnecessarily. Avoid unnecessary tables to join, limit access to only the needing objects. Avoid using PL/SQL or prodedural languages when you can write it in SQL. Sometimes issues are solved with very basic concepts knowledge.