Tag Info

New answers tagged

0

For the record, there is very important reason why DELETE FROM table1 where id NOT IN (SELECT id FROM table1 group by phone); does not work. MySQL processes subqueries in such a way that rows can sometimes disappear during its optimization and execution. This is not harmful to SELECT queries using subqueries in its WHERE clause. This does affect DELETE ...


0

Another way, using group by, if you don't care about other fields' values: CREATE TABLE t1 LIKE t; INSERT INTO t1 SELECT * FROM t GROUP BY phone_number; RENAME TABLE t TO t_old, t1 TO t; DROP TABLE t_old; If the table t is huge, you may want to create it without indexes and after populating the data, you create the indexes: CREATE TABLE t1 AS SELECT * ...


3

Given this sample data: root@localhost:playground > select * from t; +------+------+--------------+ | id | name | phone_number | +------+------+--------------+ | 1 | A | 111111111111 | | 2 | B | 222222222222 | | 3 | C | 222222222222 | | 4 | D | 111111111111 | | 5 | E | 222222222222 | +------+------+--------------+ 5 rows ...


0

What maintenance should be performed on the table after a massive record deletion? When performing a large delete like this, SQL Server doesn't actually remove the data from the pages for you right away. It marks them as logically deleted and the ghost cleanup task will then remove them as it runs in the background. That said, here are two questions ...


3

You can rebuild or defrag, depending on the nature of the data that was deleted, the number of indexes, and how badly they were impacted. If you know the fragmentation before the delete, it would be easy to assess the delta from sys.dm_db_index_physical_stats; if you don't, then you could just apply the same rules you normally would when determining whether ...


0

Just get the front end to display the data from the last 30 days. The delete at lesuire perhaps once a week depending on resources


0

You do not want a trigger to micromanage such deletions. I have an old post where I created a MySQL Event to delete rows for you (MySQL Event does not run) Here is an event to delete data older that 30 days every hour starting from midnight tonight USE mydata DELIMITER $$ CREATE EVENT delete_30_day_old_data ON SCHEDULE EVERY 1 HOUR STARTS CURDATE() + ...


1

I don't think it's critical to have the old rows at the very moment they turn 30 days old - a few seconds/minutes/hours delay will probably not hurt you at all. Therefore I'd remove old rows with some scheduled task/query run at some fixed interval. Otherwise you'll end up running additional query for every page view that you get.


0

Actually I had to replace the log file to another drive which has space. Then I was able to delete the things and shrink until I freed space in DATA drive.


0

The EXPLAIN took so long because it evaluated the subquery. Perhaps the subquery took so long because of lack of INDEX(field, id). When deleting a large chunk of a table, it is often faster to copy everything that you want to keep into a new table, then use RENAME to swap tables. Or, you could do the deletes in chunks of 100-1000, walking through the ...


2

You need to delete in smaller chunks using a loop and TOP/ROWCOUNT. I have no idea what your magic number would be, so you'll have to play with it, but the point is to cause minimal usage of the transaction log and, in simple recovery, issue multiple checkpoints in between iterations of the loop so that the log space you've used can be marked for reuse. (The ...


0

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field. I have a much better method. DROP TABLE IF EXISTS keys_to_keep; CREATE TABLE keys_to_keep ( id INT NOT NULL, PRIMARY KEY (id) ); INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field; CREATE TABLE mytable_new LIKE ...


1

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this: ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ...



Top 50 recent answers are included