Re: MySQL question - Data Cleanup ideas needed for Oracle guy doing mySQL

How auto_increment works is documented:
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

For the future cleanup, MySQL also has range partitioning: https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html

On Mon, 31 Jul 2023 at 17:05, Chris Taylor <christopherdtaylor1994_at_gmail.com> wrote:

> I know this is a bit odd for the Oracle-L list, but as a long time Oracle
> guy, I find myself supporting a lot of mysql/postgres dbs now with very
> little knowledge and am being asked about data cleanup in mysql that my Org
> wants to perform on some logging tables and such. (Of course they want to
> keep "x" number of days of data)
>
> So, in Oracle when considering deletes and truncates, where the data to be
> removed is millions of rows and we only want to keep like 5% of that data
> (or some-such), I normally recommend saving off those records they want to
> keep into a temp table and truncate the original and re-insert them back.
>
> In MySQL these tables may have an auto-increment field on the tables
> involved so I'm unsure if we'd be able to insert the data back without the
> auto-increment fields changing the data (or erroring completely).
>
> So I'm looking for "good" options in mySQL to do this for you guys that do
> a lot of mySQL :)
>
> Scenario:
> Regular hash table with 28M rows. They want to keep say , 1M rows of the
> newest data (perhaps less/probably less) but use as an example.
>
> I know we do a delete ... where <some condition> limit 1000 ; to
> incrementally delete and then I suspect we'd need to do an optimize on the
> table (another operation I'm unfamiliar with)
>
> I've seen posts/topics recommending creating an index on the field used in
> the WHERE clause but I think that's a bad idea as it incurs
> addtiional index maintenance for a delete.
>
> I've also seen posts/topics recommending creating a table with the keys
> you want to delete and doing a join from the main table to the driving
> table for the delete but I'm not convinced that's better than saving off
> the records and truncating the primary table and re-inserting.
>
> So any suggestions/thoughts/ideas are very welcome!
>
> Thanks,
>
> Chris
>
>
>
>
> --
Ilmar Kerm

--
http://www.freelists.org/webpage/oracle-l