Re: Problems with system locking
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.
Cheers
Brian
Subject
Views
Written By
Posted
10389
April 26, 2006 05:37AM
10168
April 27, 2006 04:36AM
4100
April 27, 2006 07:41AM
3494
May 02, 2006 06:13AM
3503
May 02, 2006 07:49AM
3124
May 02, 2006 10:46AM
Re: Problems with system locking
2916
May 12, 2006 04:10AM
3044
May 15, 2006 02:57AM
3112
April 28, 2006 03:23AM
2782
May 02, 2006 06:27AM
4275
June 13, 2006 09:59PM
2837
June 14, 2006 08:04AM
3125
June 14, 2006 10:22AM
2879
June 15, 2006 09:26AM
3346
June 15, 2006 11:18AM
3134
June 16, 2006 03:18AM
3243
July 14, 2006 03:52PM
3131
July 31, 2006 04:16AM
3093
August 22, 2006 11:34AM
3088
July 19, 2006 04:07AM
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.