New answers tagged delete
So, I will answer the question "why it is creating problem with whole query": In MySQL's REPEATABLE-READ transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking). As MySQL happens to be executing the read on the first table using an index scan, ...
Solution Create a TempTable(record_table_keep) with minimum id for every subid,catid combination Perform a DELETE JOIN between record_table and record_table_keep using LEFT JOIN Only delete records from record_table whose id is not in record_table_keep Proposed Query CREATE TABLE record_table_keep SELECT id,subid,catid FROM record_table WHERE 0=1; ALTER ...
DELETE FROM record_table a WHERE EXISTS( SELECT subid FROM record_table b WHERE a.subid= b.subid AND a.id < b.id )
Instead of issuing PRINT @tsql;, you can issue EXEC( @tsql );, if I understand what you're asking. If so, I would suggest declaring a preview parameter ( something like DECLARE @Preview BIT = 1; ) and then use an IF to either print or execute. IF ( @Preview = 1 ) BEGIN PRINT @tsql; END ELSE BEGIN EXEC( @tsql ); END;
Maybe you could do something like this: Add a new field called deleted. Do an update like UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'. Set cron to delete this at night time.
My first instinct would be to do multiple, smaller deletes by limiting the number of query results, and running the query multiple times: DELETE FROM `tablename` WHERE `columnname` LIKE '-%' LIMIT 1000000
The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed. In this case I would have recommended trying sequential deletes of the form: DELETE FROM `tablename` WHERE `columnname` LIKE '-a%'
I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...
Better yet, turn it into a multiple-table DELETE. That is where you have a JOIN built into the DELETE. http://dev.mysql.com/doc/refman/5.6/en/delete.html shows two syntaxes for such; you probably want the second.
Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...
Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...
OPTIMIZE TABLE is almost never needed on InnoDB. Are you DELETEing records based on age? If so, you can make the "bulk delete" essentially free by using PARTITIONing and DROP PARTITION. More details here: http://mysql.rjweb.org/doc.php/partitionmaint
Top 50 recent answers are included