In our dev in OTK ouath_token we have 4 million rows want to delete 2M from it in any order,what is the best way of doing it?pls advice
Are the tokens expired? Ideally, the maintenance jobs would be managing these, we don't typically recommend manually editing the database. If are you concerned with only expired tokens, the scheduled tasks is the best option.
Database Maintenance - CA API Management OAuth Toolkit - 4.3 - CA Technologies Documentation
If you have a backup of the database and need to edit it directly this page has the queries to purge expired tokens.
Optimization - CA API Management OAuth Toolkit - 3.6 - CA Technologies Documentation
If you need to delete X number of entries indiscriminately, you can use the limit clause:
DELETE FROM oauth_token WHERE rtoken is null limit 2000000;
If you don't care if token is expired, then it's a question for DBA.
I would not suggest using DELETE FROM clause if you try to remove big data, as it costs long time.
You can rename token table first and then insert into new table select from old table, and then drop the old table.
(ie. change delete to insert, insert will be much faster than delete.)
MySQL :: MySQL 5.7 Reference Manual :: 13.1.33 RENAME TABLE Syntax
MySQL :: MySQL 5.7 Reference Manual :: 188.8.131.52 INSERT ... SELECT Syntax
This way you need the right sql to create the new token table, "show create table" command will show the create table sql.
If don't want to touch the origin table, you can also insert into a temp table select the data wants to keep from token table -> truncate token table -> insert back data from temp table.
Both ways will be much faster than "delete from" big table.
Retrieving data ...