Re: Tunning Select and Where statements

From: Anne Crowther <anne.crowther_at_capgemini.co.uk>
Date: Tue, 28 Sep 1999 13:51:31 +0100
Message-ID: <7sqdkn$6ej$1@taliesin.netcom.net.uk>

Hi,

> Why would an index on 'age' in WHERE clause result in a slower query than
> having no index.

This would happen if a significant proportion of the people in your table were of age 21 (say, over 15%). In that case, using the index results in more I/O than doing a full table scan.

> Why would an index on both 'name' and 'age' result in a faster query.

Assuming you created the index on the columns in the order age, then name, then the query could run faster because all the values for name can be got from the index itself. This is quicker than looking up the rowid in the index and then going off to the table to look up the name from there.

Anne Crowther
--
mailto: anne.crowther_at_nospam.capgemini.co.uk (Remove the nospam bit first :-) )

>
> Thanks for any Help
>
>
> Ming.
>
>
Received on Tue Sep 28 1999 - 07:51:31 CDT