MySQL Forums
Forum List  »  Performance

Re: UPDATE 6m records quickly
Posted by: James Day
Date: January 26, 2005 03:41AM

From the sound of it you would be better off using InnoDB rather than MyISAM for this application. Assuming that your trends are mostly prices for a particular stock (price charts and such). Make a unique ID for ths stock (not the current name because those change, ideally a unique integer you link to the current name with joins) and have that and some time value as the primary key. Now InnoDB will store adjacent values for the same stock in the same database page so accessing adjacent values will be very fast. No need for an ALTER TABLE ... ORDER By any more if this is your usual desired order (and that command is ineffective for InnoDB).

I have a similar application (old versions of articles) where they are currently stored in time sequence order and we're changing the primary key to store them in article then time sequence order, so the adjacent entries for each article are stored together. That will change adjacent article access from one disk seek per version to needing a new seek only when the desired version sequence spans multiple pages.

Konstantin knows much more than I do about cursor plans.

Options: ReplyQuote

Written By
January 23, 2005 03:47PM
January 24, 2005 01:04AM
January 24, 2005 03:51PM
January 24, 2005 03:53PM
January 24, 2005 09:43PM
January 25, 2005 12:47AM
January 25, 2005 02:41PM
January 25, 2005 11:32PM
Re: UPDATE 6m records quickly
January 26, 2005 03:41AM

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.