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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
