MySQL Forums
Forum List  »  MyISAM

Re: Problems with system locking
Posted by: Brian O'Donnell
Date: May 12, 2006 04:10AM

Hi Ingo

You hadn't mentioned MERGE tables but we've subsequently investigated them and they might provide a workaround for the locking problem. We are going to try changing the UNION=(x,y,z) part of the merge table 'on the fly' to add a new table into the merge table setup. We tried using views but frankly it was a non starter for performance reasons.

Is there a way of specifying which order MySQL will use when looking into the tables? I presume it will have to examine the relevant index from each instance of the table to establish the result set etc. We tend to only need to query recent data (the rest is kept for bespoke analysis) so we know, for example, that we're only *generally* likely to need to access one or two of the many underlying tables (based for example on a date range). I guess MySQL will go and examine all the underlying tables for each query?

We were thinking that if we set the MERGE table to use the first table in the set for INSERTs then that might improve performance, on the assumption that MySQL will look in this table first when you execute a query. However I'm not sure this will make any difference as it probably has to go to the other tables to ensure the data isn't in them - unless it builds a separate index?

Hopefully this will get us out of the problem but the fact remains that the problem exists... Unfortunately its highly unlikely we'd be able to set up a reliable test environment.


Options: ReplyQuote

Written By
April 26, 2006 05:37AM
April 27, 2006 04:36AM
Re: Problems with system locking
May 12, 2006 04:10AM
June 13, 2006 09:59PM
June 14, 2006 10:22AM
June 15, 2006 11:18AM
July 14, 2006 03:52PM
August 22, 2006 11:34AM

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.