Hi all -
Is there a good workaround for mysql's lack of 'DESC'
functionality when creating an index?

I'm looking at migrating an Oracle RAC database to
mysql (InnoDB or Cluster - testing both at the
moment), and the Oracle database uses a lot of
multi-column indexes with some colums indexed in
descending order.

If I can't emulate the descending index colums somehow
it's likely to seriously impact performance and
possibly derail the prospect of migration - help!

cheers
Iain
--

What I've done in the past is to create extra columns which contain the
reverse of a number/date used previously in an index.

So, for instance if it's a simple INT column (A) and you know the max would
be 9999999 for example, create an extra column and populate that with
(10000000 - A) and use it as an ASC index.

Same can be done with dates.

Not always applicable, but it works and is fairly easy to implement.

Phil

On Tue, May 20, 2008 at 2:20 PM, Bof <bof90@yahoo.com> wrote:

> Hi all -
> Is there a good workaround for mysql's lack of 'DESC'
> functionality when creating an index?
>
> I'm looking at migrating an Oracle RAC database to
> mysql (InnoDB or Cluster - testing both at the
> moment), and the Oracle database uses a lot of
> multi-column indexes with some colums indexed in
> descending order.
>
> If I can't emulate the descending index colums somehow
> it's likely to seriously impact performance and
> possibly derail the prospect of migration - help!
>
> cheers
> Iain
> --
>
>
>
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
>



--
Help build our city at http://free-dc.myminicity.com !

Hi Phil -

Thanks for the suggestion. Might that involve possibly
changing queries in the web application hitting the
database so that it uses the new column, or would the
indexing on the new column take care of speeding up
the existing queries?

cheers
Iain
--


--- Phil <freedc.bok@gmail.com> wrote:

> What I've done in the past is to create extra
> columns which contain the
> reverse of a number/date used previously in an
> index.
>
> So, for instance if it's a simple INT column (A) and
> you know the max would
> be 9999999 for example, create an extra column and
> populate that with
> (10000000 - A) and use it as an ASC index.
>
> Same can be done with dates.
>
> Not always applicable, but it works and is fairly
> easy to implement.
>
> Phil
>
> On Tue, May 20, 2008 at 2:20 PM, Bof
> <bof90@yahoo.com> wrote:
>
> > Hi all -
> > Is there a good workaround for mysql's lack of
> 'DESC'
> > functionality when creating an index?
> >
> > I'm looking at migrating an Oracle RAC database to
> > mysql (InnoDB or Cluster - testing both at the
> > moment), and the Oracle database uses a lot of
> > multi-column indexes with some colums indexed in
> > descending order.
> >
> > If I can't emulate the descending index colums
> somehow
> > it's likely to seriously impact performance and
> > possibly derail the prospect of migration - help!
> >
> > cheers
> > Iain
> > --
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
>
http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
> >
>
>
>
> --
> Help build our city at http://free-dc.myminicity.com
> !
>

yes, you'd have to alter the queries to use the new index. As I say it's
very application dependent and does not always apply, but you can normally
shoehorn any application to use it.

Phil

On Wed, May 21, 2008 at 9:22 AM, Bof <bof90@yahoo.com> wrote:

> Hi Phil -
>
> Thanks for the suggestion. Might that involve possibly
> changing queries in the web application hitting the
> database so that it uses the new column, or would the
> indexing on the new column take care of speeding up
> the existing queries?
>
> cheers
> Iain
> --
>
>
> --- Phil <freedc.bok@gmail.com> wrote:
>
> > What I've done in the past is to create extra
> > columns which contain the
> > reverse of a number/date used previously in an
> > index.
> >
> > So, for instance if it's a simple INT column (A) and
> > you know the max would
> > be 9999999 for example, create an extra column and
> > populate that with
> > (10000000 - A) and use it as an ASC index.
> >
> > Same can be done with dates.
> >
> > Not always applicable, but it works and is fairly
> > easy to implement.
> >
> > Phil
> >
> > On Tue, May 20, 2008 at 2:20 PM, Bof
> > <bof90@yahoo.com> wrote:
> >
> > > Hi all -
> > > Is there a good workaround for mysql's lack of
> > 'DESC'
> > > functionality when creating an index?
> > >
> > > I'm looking at migrating an Oracle RAC database to
> > > mysql (InnoDB or Cluster - testing both at the
> > > moment), and the Oracle database uses a lot of
> > > multi-column indexes with some colums indexed in
> > > descending order.
> > >
> > > If I can't emulate the descending index colums
> > somehow
> > > it's likely to seriously impact performance and
> > > possibly derail the prospect of migration - help!
> > >
> > > cheers
> > > Iain
> > > --
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> >
> http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
> > >
> >
> >
> >
> > --
> > Help build our city at http://free-dc.myminicity.com
> > !
> >
>
>
>
>
>


--
Help build our city at http://free-dc.myminicity.com !

On Tue, May 20, 2008 at 11:20 AM, Bof <bof90@yahoo.com> wrote:
> Hi all -
> Is there a good workaround for mysql's lack of 'DESC'
> functionality when creating an index?
>
> I'm looking at migrating an Oracle RAC database to
> mysql (InnoDB or Cluster - testing both at the
> moment), and the Oracle database uses a lot of
> multi-column indexes with some colums indexed in
> descending order.
>
> If I can't emulate the descending index colums somehow
> it's likely to seriously impact performance and
> possibly derail the prospect of migration - help!
>
> cheers
> Iain

I have not yet run into performance issues with indexes not being
stored ascending.

Running queries with ORDER BY indexed_field ASC appears to me to have
the same or near the same performance characteristics as ORDER BY
indexed_field DESC. What are the circumstances where this crops up as
an issue?

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)