Re: DESC index column
On Tue, May 20, 2008 at 11:20 AM, Bof <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 999 for example, create an extra column and > > populate that with > > (1000 - 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 > > <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > > > > > > > > > > -- > > Help build our city at http://free-dc.myminicity.com > > ! > > > > > > > -- Help build our city at http://free-dc.myminicity.com !
Re: DESC index column
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 <[EMAIL PROTECTED]> 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 999 for example, create an extra column and > populate that with > (1000 - 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 > <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > > > > > -- > Help build our city at http://free-dc.myminicity.com > ! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESC index column
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 999 for example, create an extra column and populate that with (1000 - 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > -- Help build our city at http://free-dc.myminicity.com !
DESC index column
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/[EMAIL PROTECTED]
