Ignoramus23984 <ignoramus23984NOSPAM.23984.invalid> wrote:Hmm. I guess it is MyISAM?>
> I have a large table, currently about 12 gigabytes. It is alled
I'm quite sure that your datafile is fragmented. Could you post the> create table bincache (
> id VARCHAR(250) PRIMARY KEY,
> dataclass VARCHAR(32),
> lastdate DATETIME,
> lastused DATETIME,
> dataitem blob
> It stores various expensive to obtain (compute or download)
> stuff. Every night I clean it by deleting data items that are over,
> say 2 weeks old (using lastdate field).
result of SHOW TABLE STATUS LIKE 'bincache'? Have a look at the
`Data_length` and `Data_free` columns. If the latter is big (bigger
than the first) your table is fragmented. To defragment it, you can
use OPTIMIZE TABLE.
You say you DELETE daily, everything older than 2 weeks. That means> My issue is that delete takes too long. I would like to know if, say,
> creating index based on lastdate would alleviate the problem.
you delete 1/15th of your rows on average. Probably an index on
`lastdate` (IMHO `lastused` would be better) could speedup the
selection of those rows. You already have significant costs for
updating the primary key so another index would not be too bad.
I would suggest to delete less often, say once a week and use the
1. create a second, identical table bincache_new
2. select all rows to be preserved from bincache and
insert them into bincache_new (use INSERT ... SELECT ... WHERE)
3. rename tables: bincache -> bincache_drop, bincache_new -> bincache
4. drop bincache_drop
That way you avoid fragmentation at all. Plus you get a nice clean
fresh index distribution every time.
Axel Schwenke, Senior Software Developer, MySQL AB
Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]