> Were the changes to the indexes in 2.0 suppose to provide more than
> larger indexes?

Correct.

The new v2.0 index structures address these issues:

- Increase maximum key size to 1/4 page size from 253 bytes

- Add segment selectivity for multi-segment indexes, which improves the
choices that the optimizer will make in using the best indexes.

- Improve the performance of index insert/update/deleted operations for
poor-selectivity indexes. (This is the reason that the restore time
dropped from 50 minutes to 26 minutes)

> The reason I'm asking is because some of my queries still run just
> as slow in 2.0 as they did 1.5.2.
>
> Ex. SELECT distinct store_no from mytable; (Does a table scan)
>
> mytable has 2.1 million records with an index on store_no.
> It takes 3.32 minutes to run.
>
> On the database I want to convert from, it takes 15 secs. (Data is
> exactly the same between the two databases)

I suspect that you are not comparing a MGA database (multi-generational
database -- like InterBase or PostgreSQL). Could you provide the name
of the other database?

I'll spare us all the intricacies of the nature of an index in MGA based
database, but suffice it to say, that the index provides 'guidance' to
the engine regarding the rows which should match the criteria. The
engine must, however, read each data row to confirm the actual field
value.

> SELECT * from mytable where store_no > 2; (Uses store_no index)
>
> It takes 3.27 minutes
> On the database I want to convert from, it takes 1.57 minutes.

Could you try:

SELECT COUNT(*) from mytable where store_no > 2;

This will provide a fairer comparison between the performance of the
engines to resolve the result and the communication protocol to transfer
the result set from the client to the server.


Sean