In the last episode (Oct 09), James Eaton said:
> From: "Dan Nelson" <dnelson@stripped>
> >I don't think that the number of files has any impact on query
> >speed. The advantage file-per-table gives you is the ability to
> >recover unused space easily by running OPTIMIZE TABLE. With a
> >single tablespace, the only way to recover space is to dump all the
> >tables, delete all the tablespace files, and reload.
> Are you saying OPTIMIZE TABLE has no effect when there's a single
> tablespace, or are you saying that running an OPTIMIZE table causes
> MySQL to go through all those gyrations?
When there's a single tablespace, OPTIMIZE TABLE will compact and
rebuild your table and indexes just like normal, but since the
containing tablespace cannot shrink, mysql can't return the freed space
back to the OS.
> So then what are the advantages of having a single tablespace over
> file-per-table? I'd assume there must be some if that's the default
Single-tablespace was the first implementation, that's all. One
advantage it does have is if you aren't using RAID, or have multiple
slow RAID volumes, you can use single-tablespace mode with multiple
tablespace files on different disks to improve performance.