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 <[email protected]> escribió: Asunto: [Maria-discuss] Help with history tables Para: "Maria Discuss" <[email protected]> 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 : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
