> > > > Sorry for answering before properly reading the question...
> > > >
> > > > No, a DESCending index cannot be used with an ASCending ORDER
> BY.
> > > You
> > > > need two separate indexes if you want to index the field both
> > > > ASCending and DESCending.
> > >
> > > > Though note that too many indexes can be as
> > > > bad as too few indexes, create only those that are likely to be
> > > > selective and useful.
> > > >
> > > > Set
> > > >
> > >
> > > Indeed. This is the real problem. I display the data (let's say
> 50,
> > > 000) rows in a grid with let's say 10 columns. Now the grid can be
> > > sorted by every column in ASC or DESC order. Imagine how this will
> > > look for 15 columns where I would have 30 indexes that slow down
> my
> > > INSERTS, UPDATES and are very large because I have 50,000 rows.
> > >
> > > I don't get the reason for this unidirectional approach but to me
> is a
> > > pain in the proverbial arse.
> >
> > Can't be as bad as showing 50000 rows in a grid :-)
> >
> > > Maybe there is another way of doing this, but the nature of the
> data
> > > and of the software application forces me to do this.
> >
> >
> > Sorting is independent of indices. If you want to retrieve all the
> rows,
> > fetching them via an index is slower than fetching them straight
> from
> > disk and sorting them afterwards.
>
> My explanation was ambiguos. Let's try to clear the smoke:
>
> 1. I have a set of tables linked with several joins. The important
> thing to know is that they are huge.
> 2. The tables have a lot of columns, and most of them are shown in the
> grid to the user.
> 3. I only display the rows that are visible (eg. fit in the screen)
> and of-course keep a local cache not to transfer them again.

Are you showing (part of) a 50000 rows resultset or not? If so: don't.
Not even if you only "show" 50 visually. The server has to prepare the
resultset for you, this will take time.

> Now: making two sets of indices for every shown column is going to
> make my INSERTS/UPDATES slow and increase the size of the database.
>
> Is there any other way to do this???

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com