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 <[EMAIL PROTECTED]> 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" <[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] > > -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
