Index usage - dumb DB

[ChrisPaulson]

The database is a bit poor on index usuage and therefore has bad performance.

If I had a table with three fields a,b,c and I had an index on those three fields I could use a query like: -

for each table where
(a = 1 ) and
(b = 1 ) and
(c = 1 )...

and it would use an index - easy.

I could do

for each table where
(a = 1 ) and
(b = 1 ) and
by c

and it would use an index - again easy.


for each table where
(a = 1 ) and
(b = 1 ) and
by c descending.

and it would NOT use an index - pants.

To get it to use an index you have to do: -

for each table where
(a = 1 ) and
(b = 1 ) and
by a desending by b descending by c descending.

Which returns the same results, this is very poor as it should know that the equality matches can be used in the by.

The DB does not help to optimise queries - not so much a 4GL as a 3GL with more typing. :blue: