Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


14 - Understand ASC And DESC

An index will help optimize an ORDER BY clause if it is sorted in the same or exactly opposite direction as the ORDER BY clause. For a single-column index and ORDER BY clause this boils down to a simpler rule: it doesn't matter whether you use ASC or DESC when defining the index. Either choice will make the ORDER BY run faster.

It gets a bit more complicated for multi-column indexes and ORDER BY clauses. Figure 14 shows that an ASC index on two columns helps speed up queries that specify the same value of ASC or DESC for both columns, but not if the ORDER BY says ASC for one column and DESC for the other.

Figure 14 - Multi-Column Index And ORDER BY 
CREATE TABLE order1

   ( primary1 INTEGER NOT NULL,

     sort1 INTEGER NOT NULL,

     sort2 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE INDEX iorder1 ON order1

   ( sort1 ASC, sort2 ASC );


SELECT * FROM order1 ORDER BY sort1 ASC, sort2 ASC;

   Estimated 10000 rows in query (I/O estimate 497)

   PLAN> order1 (iorder1)


SELECT * FROM order1 ORDER BY sort1 DESC, sort2 DESC;

   Estimated 10000 rows in query (I/O estimate 497)

   PLAN> order1 (iorder1)


SELECT * FROM order1 ORDER BY sort1 ASC, sort2 DESC;

   Estimated 10000 rows in query (I/O estimate 13215)

   PLAN> TEMPORARY TABLE order1 (seq)


SELECT * FROM order1 ORDER BY sort1 DESC, sort2 ASC;

   Estimated 10000 rows in query (I/O estimate 13215)

   PLAN> TEMPORARY TABLE order1 (seq)

If the ORDER BY says ASC for one column and DESC for the other, then the index must say the same thing for it to be useful. It doesn't matter which column gets the ASC and which gets the DESC, what matters is that the index as a whole must be sorted in the same or exactly opposite direction as the ORDER BY clause.

[Home] [Table of Contents] [Previous Section] [Next Section]