Re: Randomization as a cache clearing mechanism

I don't know MySQL well at all, so my comments may be off-base.

First off, things really depend on the table schema and the way the table is used. From my experience with Sybase I'd say that purging the table might not gain you much in terms of fetch times, and the deletes might actually take up more resources than you're likely to save.

I did a check recently for a fairly large table (15+ million rows), and when a fetch is done based on a unique index the number of IO operations is quite small (4 IOs or so, IIRC). I don't know if you can get this sort of statistics for MySQL, or how large this table is, but I'd check that sort of thing before doing anything else.

I also don't know if MySQL generates any sort of locking contention when you start deleting a (comparatively) large number of rows - that's another thing that you should consider.

Michael (who has to rush off to dinner now... :-)

Comment on Re: Randomization as a cache clearing mechanism

Replies are listed 'Best First'.
Re^2: Randomization as a cache clearing mechanism by dragonchild (Archbishop) on Nov 19, 2004 at 17:50 UTC
I also don't know if MySQL generates any sort of locking contention when you start deleting a (comparatively) large number of rows - that's another thing that you should consider. Depending on the table type used, it can be a big or small issue. MyISAM is designed from sequential adds, few deletes, and a bazillion reads. If you start deleting bunches of stuff, you can actually prevent reads from happening. InnoDB is better about that, but has its own overhead issues due to ACID compliance and row-level locking. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]

Replies are listed 'Best First'.

Re^2: Randomization as a cache clearing mechanism
by dragonchild (Archbishop) on Nov 19, 2004 at 17:50 UTC

I also don't know if MySQL generates any sort of locking contention when you start deleting a (comparatively) large number of rows - that's another thing that you should consider.

Depending on the table type used, it can be a big or small issue. MyISAM is designed from sequential adds, few deletes, and a bazillion reads. If you start deleting bunches of stuff, you can actually prevent reads from happening. InnoDB is better about that, but has its own overhead issues due to ACID compliance and row-level locking.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]