yes :) but that's a start point index optimizations can be done with time
2014-05-21 20:36 GMT-03:00 Federico Razzoli <[email protected]>: > Maybe. But it cannot bet optimized. All the discarded rows must be read, > before reaching the offset (and I suppose they're not in the buffer pool). > > Federico > > > -------------------------------------------- > El jue, 22/5/14, Roberto Spadim <[email protected]> escribió: > > Asunto: Re: [Maria-discuss] Help with history tables > Para: "Federico Razzoli" <[email protected]> > CC: "Maria Discuss" <[email protected]> > Fecha: jueves, 22 de mayo, 2014 00:47 > > 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 > SPAEmpresarialEng. 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
