maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi guys
I have an doubt about design

I have a history table:
CREATE TABLE HISTORY(
CHANNEL INT NOT NUL DEFAULT 0,
HISTORY_DATE DECIMAL(22,6) NOT NULL DEFAULT '0' COMMENT 'UNIX TIME',
VALUE DECIMAL (22,6) NOT NULL DEFAULT 0,
PRIMARY KEY (CHANNEL,HISTORY_DATE)
)

my question is...
i need a history of only 50000 rows for each channel
what's the best method to allow a good history without many deletes,
analyse tables, and others stuffs to allow a good table/disk usage?

i was considering a counter, for example, after 1000 inserts, run a
SELECT COUNT(*) FROM HISTORY WHERE CHANNEL={channel_id}

get this number of rows, if it's > 50000 run:

DELETE FROM HISTORY WHERE CHANNEL={channel_id} ORDER BY HISTORY_DATE ASC
 LIMIT (number_of_rows - 50000)

any other idea?

maybe a 'batch' operation?
i was think about something like (don't work, just a idea)

DELETE FROM HISTORY WHERE
CHANNEL IN (channel numbers)
AND xxxxx? LIMIT 50000 GROUP BY CHANNEL

i don't know anything to group by channel id and delete rows using only one
sql command , any idea?


thanks guys!

-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle