=] sqlite have it http://www.sqlite.org/lang_delete.html
2014-05-21 19:47 GMT-03:00 Roberto Spadim <[email protected]>: > 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 > -- 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
