At 09:06 AM 10/12/2004, you wrote:
>Smaller tables = smaller indexes. Smaller indexes also mean faster
>look-ups and faster record inserts. You could eventually drop indexes on
>the older tables, saving disk space (by comparison, you can't index only
>part of a table). Once a table becomes so old that no updates will be
>performed on it, you can even compress it saving additional disk space.
>Tables that are rarely used can be moved into near-line storage (a Network
>share or a SAN device) so that you save the faster local disk for the
>other 95% of your queries.
>Unimin Corporation - Spruce Pine
>"Ronnie Sengupta" <ronnie_sengupta@stripped> wrote on 10/12/2004
> > "Does splitting a large table (20 Million rows) growing at 5 million or
> > a month into smaller tables improve performance given that the table can
> > split in a logical way such that 95% queries don't need to look at data
> > spanning across the split tables"
One other benefit to smaller tables is the entire table may fit into the
query cache so Select queries will be quite fast. If you don't have enough
ram, a larger table would only be partially in the cache so this may make a
difference. (If the table is updated, the query cache is flushed so the
query cache is really only effective when tables are not being updated
much.) Also smaller tables will occupy less disk space so if the drive is
defragged, there is less work for the hard drive to find the data. You can
experiment with smaller tables and use Merge tables when appropriate.