anything like delete from history where channel_id=10 order by date_time desc limit 10000000000 offset 50000 ? i never checked offset at delete statment
2014-05-21 18:43 GMT-03:00 Federico Razzoli <[email protected]>: > 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 > > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
