Your mileage may vary from Paul's answer...
We have a bunch of users with small local MySQL databases using MyISAM
on various flavors of Windows. The users need to be able to work
offline and synchronize their data with a server when they have Internet
access.
Without flushing, even if the application had been idle for some time,
tables would get corrupted in case of loss of power or one of Windows 98's
famous freeze-ups. The base tables were corrupted, not just the indexes,
so we couldn't recover by just running myisamchk. We now do
a FLUSH TABLE nnn (for each modified table) after handling the
user's save request, and we've had no further problems.
For our server, we use InnoDB tables, which presumably recover properly
after a power failure or system crash, so we shouldn't need to do
an explicit FLUSH. I don't know if FLUSH TABLE does anything in
Innodb. Of course, the server (Linux) has never crashed...
The following from the 3.23.49 manual--it seems to have disappeared
from the current manual. Evidently, at least for MyISAM, and despite the
statement in the manual, the data files are not flushed between queries.
Of course, doing so could slow things down a lot.
The ISAM table handler -- Stable
[...] The only known way to get a corrupted table is to kill the
server in the middle of an update. Even that is unlikely to destroy
any data beyond rescue, because all data are flushed to disk between
each query. [...]
The MyISAM table handler -- Stable
[...] largely based on the ISAM table code [...]
> Date: Tue, 6 Aug 2002 20:05:21 -0500
> To: ewen.marshall@stripped, MySQL <win32@stripped>
> From: Paul DuBois <paul@stripped>
> Subject: Re: Flush
>
> At 14:20 +0100 8/5/02, Ewen Marshall wrote:
> >Folks,
> >
> > Should you always flush a table after an update in mySQL?
>
> Only if you want your server's performance to go into the tank.
>
> Flushing shouldn't be necessary; let the server do it as necessary.
>
> >
> >Ewen
