Re: Problems with system locking

[Brian O'Donnell]

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