Oracle Database Performance Tuning: Descending Indexing

Many database administrators consider the role of descending indexes and the scanning of indexes that are loose. Some call it better range support because of their lack of relation to database tables in general, and MySQL should be having such support for each storage engine sooner or later.

Before we discuss this, it is important to know what descending indexes are. These were something that MySQL did not support at one point but it does as of version 8.0, and DESC used while defining an index is not overlooked anymore; it results in key values being stored in descending order.

Earlier, indexes took longer to be scanned in reverse order, increasing the need for Oracle database performance tuning. This performance lag could also be compensated by scanning a descending index in a forwarding order.

A descending index is way less problematic than many think and it also enables the use of indexes with multiple columns that entail the mixing of scan orders for various columns - ascending or descending order - depending on which order is more efficient per column.

However, the way an indexed ORDER BY col DESC or ascending query is optimized could also depend on the operating system and storage engine. For instance, some OSs may not allow read-ahead actions in backward direction due to performance issues, whereas storage engines like MyISAM might have significantly slower reverse scan performance for packed indexes as compared to forward scans.

What type of situations call for descending indexes? One of the best examples is when the user requires two columns to be arranged in different orders, like the price column being arranged in ascending order while the date column lined up in descending order, but the related queries may be difficult to optimize or perform SQL performance tuning on because it will require the external "filesort". However, if we can create an index query with price ASC, date DESC, then the query will easily display data as needed.

Before descending indexes were available, this situation required a workaround with elements like "reverse_date". In MySQL 5.0, the user can also make use of triggers to slightly simplify things.

Let us talk about loose index scans - a loose index scan is something newer users may also encounter during their use of MySQL and may wonder how it helps with Oracle database performance tuning. Actually, it doesn’t if you use range clauses like "" but only with the "=", so keep that in mind when you design a new application or try to port them from different databases. Any clause that follows the range in an index fails to make use of that range in its operation, so try to use range clauses only towards the end in your queries.

If we take an example into consideration, let us assume that there is a key (x,y,z) in which x=2, y=3, and z=4. If we use an index to determine whether x=2 and y>3, then z=4 will not require an index where the rows with all z values that may need to be retrieved through the index. If it is not the query that covers the required index, you may consider decreasing it to key(x,y) to maintain a smaller index.

Loose index scan implementation definitely helps in SQL performance tuning, and in MySQL 5.0 it was applied for aggregate queries of an extremely limited case. An entire loose index scan was among the most anticipated aspects of MySQL optimization back when it wasn’t available.

Stay tuned for much such database performance tuning tips.