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 <roberto@spadim.com.br> escribió:
Asunto: Re: [Maria-discuss] Help with history tables
Para: "Federico Razzoli" <federico_raz@yahoo.it>
CC: "Maria Discuss" <maria-discuss@lists.launchpad.net>
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 <federico_raz@yahoo.it>:
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
--
Roberto Spadim
SPAEmpresarialEng. Automação e
Controle