Solving Bottlenecks With SQL Indexes and Partitions

The article shows that indexes help most when a query narrows the search range and can follow the order of indexed values. It contrasts proper range predicates with expressions that block index use, and it shows how wildcard placement in LIKE changes whether the index can stop early or must be scanned in full.

Reading notes

#

• Range queries benefit from indexes when they reduce the possible values of one or more columns.
• A predicate that applies a function to the column can prevent the RDBMS from using the index efficiently.
• Rewriting a date equality condition as a true range query lets the engine use the sorted index entries.
• LIKE '%son' can require scanning the whole index or table, depending on how many rows match.
• LIKE 'Rob%' can use the index to find the first matching entry and stop when the prefix no longer matches.
• For indexed range queries, the read cost is described as B = D + 1 + R, where D is B-tree depth and R is returned rows.
• Only the B-tree depth changes with table size in that estimate, so smaller depth helps most when result sets are small.
• Index fragmentation grows over time as inserts, updates, and deletes split blocks and leave them half-full.
• Reindexing restores physical order, but it can require locking and is best done during off-peak hours.
• Deleting historical rows can make fragmentation worse and increase the need for reindexing.
• Partitioning can limit active data and make maintenance easier.
• Global indexes cover all partitions but are not partition-aware, so dropping a partition still requires rebuilding affected index data.
• Globally partitioned indexes let the index follow the table partitioning, so dropping a partition can be handled without downtime.
• Local indexes live inside each table partition and avoid the drawbacks of global partitioned indexes.
• Local indexes work well when most queries touch only one or a few partitions.
• Local indexes can be slower for queries that must search across every partition, such as looking up a client and hotel across the whole reservation history.
• The article closes by pointing to related topics such as caching, non-B-tree indexes, clustered indexes, functional indexes, partial indexes, and other partitioning strategies.