SEO

#1 (**permalink**) 02-28-2008, 10:32 AM

I have a table called "bincache" that holds some things that are
expensive to compute. The rows have a "last_used" date that signify
when the data was last used. Anything not accessed longer than,
perhaps, a month, can be deleted.

The problem is that the table is massive and deletes are very
expensive.

I recall that MySQL has some means of keeping data in separate files
or something depending on some column. So if I issue a DELETE
statement and its WHERE clause happens to refer to contents of a
particular file, the file would be deleted and it would be quick.

I forgot what it is called, clustering or whatever. Any idea if MySQL
supports something like that?

i

#2 (**permalink**) 02-28-2008, 10:32 AM

On Tue, 29 Jan 2008 23:09:24 +0100, Ignoramus6796
<ignoramus6796@NOSPAM.6796.invalid> wrote:

> I have a table called "bincache" that holds some things that are
> expensive to compute. The rows have a "last_used" date that signify
> when the data was last used. Anything not accessed longer than,
> perhaps, a month, can be deleted.
>
> The problem is that the table is massive and deletes are very
> expensive.
>
> I recall that MySQL has some means of keeping data in separate files
> or something depending on some column. So if I issue a DELETE
> statement and its WHERE clause happens to refer to contents of a
> particular file, the file would be deleted and it would be quick.
>
> I forgot what it is called, clustering or whatever. Any idea if MySQL
> supports something like that?

Are you perhaps referring to foreign key constraints & on delete cascades?
--
Rik Wasmus

#3 (**permalink**) 02-28-2008, 10:32 AM

On Tue, 29 Jan 2008 23:16:01 +0100, Rik Wasmus wrote:
> On Tue, 29 Jan 2008 23:09:24 +0100, Ignoramus6796
><ignoramus6796@NOSPAM.6796.invalid> wrote:
>
>> I have a table called "bincache" that holds some things that are
>> expensive to compute. The rows have a "last_used" date that signify
>> when the data was last used. Anything not accessed longer than,
>> perhaps, a month, can be deleted.
>>
>> The problem is that the table is massive and deletes are very
>> expensive.
>>
>> I recall that MySQL has some means of keeping data in separate files
>> or something depending on some column. So if I issue a DELETE
>> statement and its WHERE clause happens to refer to contents of a
>> particular file, the file would be deleted and it would be quick.
>>
>> I forgot what it is called, clustering or whatever. Any idea if MySQL
>> supports something like that?
>
> Are you perhaps referring to foreign key constraints & on delete cascades?

Nah, he's probably thinking he needs Chapter 16.

http://dev.mysql.com/doc/refman/5.1/...titioning.html

I don't think it'll help him because I imagine he's looking for ways
to partition data by relative values (at some layer of indirection,
anyway), and I don't see a convenient way to do that.

Unfortunately, this is probably going to be another case where the right
answer is going to be to step back a bit and tweak the design a minor
amount, resulting in massive code changes.

Uhm, guess what kind of project *I'M* working on this week? (:

--
72. If all the heroes are standing together around a strange device and begin
to taunt me, I will pull out a conventional weapon instead of using my
unstoppable superweapon on them.
--Peter Anspach's list of things to do as an Evil Overlord

#4 (**permalink**) 02-28-2008, 10:32 AM

On 2008-01-30, Peter H. Coffin <hellsop@ninehells.com> wrote:
> Nah, he's probably thinking he needs Chapter 16.
>
> http://dev.mysql.com/doc/refman/5.1/...titioning.html

This is it! Thanks a lot!!!!!!

My installs of MySQL do not support it, yet, but hopefully ubuntu 8.04
will support it.

I am setting up a new bad ass colo server and this will come rather handy.

i

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to make a table separated by months