I would use a batch operation (a cron job or an SQL event) which is triggered when the workload is low. If the queries are still a problem, you could relax the 50000 limit and use a statistical method. For example you could periodically compute how many rows per day are inserted (globally if possible, otherwise for each channel). You could do this once a month, for example. In the meanwhile, based on the last calculated AVG, you can use faster DELETEs based on the DATE column. You can do further optimizations, like cleansing some channels on monday, others on tuesday, etc. Everything depends on your workload and the speed of your COUNT(*)s and DELETEs.

Federico


--------------------------------------------
El mié, 21/5/14, Roberto Spadim <roberto@spadim.com.br> escribió:

 Asunto: [Maria-discuss] Help with history tables
 Para: "Maria Discuss" <maria-discuss@lists.launchpad.net>
 Fecha: miércoles, 21 de mayo, 2014 22:32

 Hi guysI 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 channelwhat'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 aSELECT 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

 SPAEmpresarialEng. Automação e Controle

 -----Adjunto en línea a continuación-----

 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp