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" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
