At 07:07 PM 19-03-02 +0800, you wrote:

> > Probably not. You would likely be better to remove that index and replace
> > it with one on (FOLDER_ID,PRI_KEY) to improve the selectivity.
>
>
>But would this actually help if I performed a query like
>
>SELECT * FROM MESSAGES WHERE FOLDER_ID=999; ?

Yes.

>(Or would it help inserts instead?)

No. Indexes aren't used for inserts (although of course they are
maintained following the commit of an insert). They are used for WHERE
searches, joins and sorts.

>I plan to have a table that run into 2 or 3 millions records.
>However, a majority of these records will have FOLDER_ID=10,
>for example. (I need to classify records into few group)

Then WHERE FOLDER_ID=10 is unlikely to be your search condition - you'll be
restricting your output sets with other WHERE criteria...so you'll need
other indexes.

It will be well worth spending the time tuning indexes. Build a test db,
populate a copy of this table it with 3 million rows and throw some typical
queries at it with a variety of index options and a timer.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________