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.