List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:October 12 2004 8:01pm
Subject:Re: splitting a large table - does it improve performance?
View as plain text  
The query cache is based on a result set size, not table size. A query
returning one row from a 100 million row table can be cached just as
easily as a row returned from a 10 row table. The difference being
modification frequency. Every time a table is modified
(update/detele/insert/replace) the query cache entries for that table
are erased.

If you are accessing most of the table (not your case since you
mentioned you are only accessing the last 5% or so) You are better off
leaving the entire table intact. The reason being indexes are cached
from the top down (they are trees remember?). Splitting the table into
mutliple smaller tables forces the key buffer to duplicate higher
level entries in the tree where it would not have had to before.

I believe (in linux at least) that files are cached into the vfs cache
on the block level so the vfs cache will work on both small and large
files.

On another note. A query on a merge table acts as a query against
every underlying table. It doesn't do any special key buffer handling
to eliminate the problem above.

The short answer is let MySQL do the work for you (except in the case
where you can pack down 95% of the table).

-Eric


On Tue, 12 Oct 2004 11:39:09 -0500, mos <mos99@stripped> wrote:
> 
> 
> 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
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 


-- 
Eric Bergen
eric.bergen@stripped
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