List:General Discussion« Previous MessageNext Message »
From:mos Date:October 12 2004 4:39pm
Subject:Re: splitting a large table - does it improve performance?
View as plain text  
At 09:06 AM 10/12/2004, you wrote:
>Absolutely!
>
>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.
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>"Ronnie Sengupta" <ronnie_sengupta@stripped> wrote on 10/12/2004
>02:11:44 AM:
>
> > "Does splitting a large table (20 Million rows) growing at 5 million or
>more
> > a month into smaller tables improve performance given that the table can
>be
> > split in a logical way such that 95% queries don't need to look at data
> > spanning across the split tables"
> >
><snip>
> >

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.

Mike 

Thread
splitting a large table - does it improve performance?Ronnie Sengupta12 Oct
Re: splitting a large table - does it improve performance?SGreen12 Oct
  • Re: splitting a large table - does it improve performance?mos12 Oct
    • Re: splitting a large table - does it improve performance?Eric Bergen12 Oct