MySQL descending indexing and mitigating index scans

Descending indexing and loose index scan

Descending indexing and mitigating index scans

Comments to I previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about Loose index Scan, or what Gokhan calls "better range" support. None of these are actially related to Innodb tables in General-these are features MySQL should get for all storage Engin Es at some point.

The comments in my previous article, especially the one mentioned in Gokhan, inspired what I wanted to write about descending indexing and mitigating index scans, or what Gokhan called "improved scope" support. Typically these features are not intrinsically linked to Innodb tables, and they can support a variety of storage engines at some point in the future.

Descending indexes-this is something MySQL does not have at this point, but it's not where as so long in large extent Because it is less problem than many people. First-if index is ascending it does not mean it can ' t being scanned in reverse and it would be. This are how MySQL would optimize indexed order by Col DESC queries for example. Reverse scan could is as fast as forward Scan-this was however where storage engines and operating systems in play. For example certain operation systems might does backward read-ahead the May which the it down a bit. Or some storage engines, such as MyISAM (for packed indexes) could have reverse scan being much slower than forward.

Descending index-now MySQL doesn't support this feature, but it's a lot less problematic than many people think. First of all, if the index is sequential does not mean that it cannot be scanned in reverse order, in fact it behaves very well. This is why MySQL optimizes the index for example, an order by Col DESC query. Reverse scans can be as fast as positive sequence scans, but these are handled by both the storage engine and the operating system. For example, some operating systems are unable to read backwards, which reduces speed. or some storage engine, such as MyISAM (which compresses the index), is slower than the positive sequence scan.

So what do you really need descending indexes? Most Typical case are when you are want to order by two colums in different directions: ... Order BY Price ASC, date DESC LIMIT The If you have indexed on (price,date) in the ascending order you are not being able to opti Mize This query well-external sort ("Filesort") would be needed. If you would is able to build index on price ASC, date DESC the same query could retrive the data in aready sorted order.

So when do we really need an inverted index? A lot of typical situations are when you want to sort two fields in different directions: ... Order BY Price ASC, date DESC LIMIT 10. If you already have a positive sequence index for (price,date), you can't better optimize the query--you need to use an external sort ("Filesort"). If you can establish the price ASC, the date DESC index, then the query can be sorted in order to take out the data.

This is however something can workaround by have something like "reverse_date" column and using it for sort. With MySQL 5.0 your even can use triggers to update it as real date updates it becomes less ugly. In the fact this are for example why you would the "Reverse_timestamp" field in the Wikipedia table structure.

However, a common workaround is to create a "reverse data" field and use it to sort. In MySQL 5.0 You can even use triggers to update real data to make it more appropriate. This is why there is a "reverse_timestamp" field in the Wikipedia table structure.

Loose index Scan-number of years ago I just started using MySQL I thought it would have any optimization which C Ould come to my mind. For example if I would have (a>0 and b>6) clause and index (A,B) I expected it would start looking in all values whe Re a>0 instantly jumping to onces have by using index. It is possibe. So I am shocked and upset to find out it did. And this optimization are still not implemented. This is very important the item to remember if you are designing your new applications or porting ones from other databases. Designing the indexes for MySQL your should only make sure queries with "=" for all keyparts in the last of the index. Only the last one was allowed to have "range" clauses, such as ", in etc. All clauses which follow is the range in the index would not be use index for their operation.

Reduce index scans--years ago, when I first started using MySQL, I thought it might be a bit of an optimization method to remember. For example, if you have one (a>0 and B>6) clause and index (A,B), I expect to be able to use the index to find all a>0 values and jump to the b>6 record immediately, which I think is possible. But what makes me depressed is not support, and this optimization method has not yet been achieved. It is important to remember this feature when designing new applications or porting databases. The MySQL index is designed with the assurance that the "=" query is used for all index parts of the final index. Only the last index section supports the "range" clause, in, and so on. All the clauses behind the range index are not used to the index.

Let me give one more example KEY (a,b,c) a=5 and b>6 with c=7 in-case index is used to check a=5 and b>6 CA Use. C=7 'll not being using the index (rows with all C values would be retrieved from the index) and if it is not the index cov Ered query you might rather shorten your key to key (A,B) to keep index smaller.

For example, in the case of indexes (A,B,CP) and a=5 and b>6 and c=7 clauses, the index retrieves the a=5 and b>6 conditions, and the c=7 does not use the index (all records containing C are retrieved from the index). This time, if any query does not need to use the full index, you can shorten the index to the key (A,B), which makes the index smaller.

The good news are Loose Index Scan implementation is finally on a way. In fact it is even implemented in the MySQL 5.0, but only for very the case of narrow aggregate.

The good news is that reducing the index scan will eventually happen in some way. MySQL 5.0 is actually implemented, but only for a few aggregate queries.

In general complete loose index scan implementation are one of my most wanted features for MySQL optimizer.

P.S If you post queries into your comments please also explain which to do your indexes on the table. Show CREATE TABLE is the best. Otherwise I can get to you wrong.

The general sense of total reduction in index scanning is the MySQL optimizer feature that I most want to implement. By the way, if you put a query in my post comments, please indicate your index, preferably with the result of show CREATE TABLE.