[GENERAL] Indexes and sorting

[John Siracusa]

Are indexes useful for speeding up ORDER BY clauses?  Example:

    CREATE TABLE t
    (
      a INT,
      b INT,
      c INT,
      d INT
    );

    SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3 ORDER BY b;

Let's say the table just has one index:

    CREATE INDEX b_idx ON t (b);

In this case, obviously the b_idx will be used and no sorting after the fact
will be required.  Now let's add an index:

    CREATE INDEX key_idx ON t (a, b, c);

On the same query, now the key_idx will be used and there'll be a sort
wrapped around it all.  The question is, is the b_idx useful at all anymore?
Can it be used to speed up the sort step?  If so, how?  If not, why not?

-John


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])