Compound index/performance

[tboyer]

I'm seeing slow performance in a SELECT that uses a compound index, i.e.

SELECT count(*), apples from table WHERE Apples in (select Type from appletypes).

The table is indexed thusly:

CREATE UNIQUE INDEX table_allfruit ON table (Apples, Peaches, Grapes)

Could either the indexing or the WHERE be improved here?

I.e. would it help to have an additional standalone index on Apples? I thought if Apples was the start of a compound index it would perform like a single index.

Or is it my IN that's causing the slowness?

Thanks for any thoughts you may have.


TB

[John]

Are you doing a GROUP BY as well? If you are using Vortex it may be faster using nested SQL rather than the IN, e.g.

<SQL ROW "select Type from appletypes">
<SQL "select count(*) N from table where Apples = $Type">
$Type: $N
</SQL>
</SQL>

[tboyer]

Thanks. It still seems slow. So a separate index on Apples (in addition to the compound index) wouldn't help?

[John]

A separate index might help if the Peaches and Grapes fields had large values in, which caused a lot more data to be read when reading the index. How much it helps will depend on the number of matching rows and size of the data.