How Indexes Can Help - Firebird

If the optimizer decides to use an index, it searches the pages of the index to find the key values required and follows the pointers to locate the indicated rows in the table data pages. Data retrieval is fast because the values in the index are ordered. This allows the system to locate wanted values directly, by pointer, and avoid visiting unwanted rows at all. Using an index typically requires fewer page fetches than “walking through” every row in the table. The index is small, relative to the row size of the table and, provided the index design is good, it occupies relatively fewer database pages than the row data.

Sorting and Grouping

When the columns specified in an ORDER BY or GROUP BY clause are indexed, the optimizer can sequence the output by navigating the index(es) and assemble the ordered sets more directly than it can with non-indexed reads.

A DESCENDING index on the aggregated column can speed up the query for the aggregating function MAX(..), since the row with the maximum value will be the only one it visits.

Joins

For joins, the optimizer goes through a process of merging streams of data by matching the values specified in the implicit or explicit “ON” criteria. If an index is available for the column or columns on one side of the join, the optimizer builds its initial stream by using the index to match the join key with its correspondent from the table on the other side of the join. Without an index on either side, it must generate a bitmap of one side first and navigate that, in order to make the selections from the table on the other side of the join.

Comparisons

When an indexed column is evaluated to determine whether it is greater than, equal to, or less than a constant, the value of the index is used for the comparison, and non- matching rows are not fetched. Without an index, all of the candidate rows have to be fetched and compared sequentially.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics