MySQL Forums
Forum List  »  MyISAM

Re: Dumping enormous MYISAM table with BLOB based on WHERE clause
Posted by: Rick James
Date: January 30, 2015 12:01PM

More minor things:

> `previewfile` longblob,
> `preview_exists` tinyint(1) default '0',

Both are NULL, you could get rid of the existence flag and simply use
previewfile IS NULL

> `id` varchar(50) NOT NULL default '',
> `list_id` varchar(50) NOT NULL default '',
> PRIMARY KEY (`id`,`list_id`)

Seems strange because (1) 2-part PK and (2) VARCHAR(50). But there is nothing 'bad' about it in this context.

> PRIMARY KEY (`id`,`list_id`)
-->
PRIMARY KEY (`id`,`list_id`, serve_date)
PARTITION BY RANGE(serve_date)...
Note: You necessarily lose the UNIQUEness check on (id, list_id). If that is a problem, it will have to be handled in code. Hmmmm... It might be possible to move the two fields to another table and have them map to an AUTO_INCREMENT on this table. The extra table would not be partitioned and would allow for the UNIQUEness test; it would (presumably) be small enough so that you don't bother ever purging it. (We would need to check numbers, like average sound-file size, # new rows per day, etc., to see if that is reasonable.)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.