MySQL Forums :: Database Design & Data Modelling :: Reverse Index columns


Advanced Search

Reverse Index columns
Posted by: jun kazawa ()
Date: February 07, 2009 04:38AM

We have a huge table that is accessed frequently read (95% at least once every 5 seconds) and write (5%).
The table is the only MySQL table (others are InnoDB) - reason being that this specific table contains a FullText index (that is not the object of the current post).

We have an old index I on it on columns (int a, datetime b, int c).
However the new design would prefer to get the index columns as (a,c,b).

What we want is the smallest disturbance users side.
And I'm afraid a Create-New-Index I2 (then Drop-old-Index I) would lock the table for quite long.

Is there a workaround - a command/option that would use a trick either
- to create the index "silently" without locking the table?
- or better, to keep (a) and reverse (b,c) - if that would be faster?

Options: ReplyQuote


Subject Written By Posted
Reverse Index columns jun kazawa 02/07/2009 04:38AM
Re: Reverse Index columns Rick James 02/07/2009 11:53PM
Re: Reverse Index columns jun kazawa 02/08/2009 06:01AM
Re: Reverse Index columns Vince Gatto 02/08/2009 12:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.