Re: Indexes and sorting

On Fri, 6 Feb 2004, John Siracusa wrote:

> 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);

If you're really doing the above alot, you probably really want (b,a,c)
which can probably avoid the sort as well (unless of course you're also
doing frequent sorts on a, etc...)

> 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?

Yes.  Queries searching on just b won't use key_idx.

> Can it be used to speed up the sort step?  If so, how?  If not, why not?

Not really at least right now.