Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: delete (reset)

MySQL DELETE Join example
+0 Vote Up -0Vote Down

A very useful helper in your join toolbox can be a delete join. Even though it’s not a special join type but a join used within DELETE statements, it’s still worth mentioning. However, from time to time when I want to make use of delete joins  on my own, I somehow managed it to forgot the syntax and have to look it up somewhere. Therefor, here is a description as well as an example.

Take care: A delete join is a very powerful weapon. And with great power comes great responsibility! I recommend to develop the delete join on a development database. At least, make sure you have a working an recent backup before trying to delete things. A delete statement that uses joins makes it easy to shot yourself in the foot. And if you do, it probably blows away your hole leg.

 

DELETE  [Read more...]

MySQL Hacks: Preventing deletion of specific rows
+3 Vote Up -0Vote Down
Recently, someone emailed me:
I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?
Frankly, I usually refer people




  [Read more...]
Duplicate entries while deleting records?
+0 Vote Up -0Vote Down


This weekend I got a call from the support engineer on duty that night. He noticed in one of our clusters all slave machines were failing replication but he could not figure out why.

At first I thought it would be an easy problem of the master and slaves being out of sync and the row-based replication failing on not finding the row, but then I noticed all machines were actually still running statement-based replication. As far as I could recall we did that to circumvent another issue that has already been solved months ago but for some reason we never put it back to row-based replication.

A simple SHOW SLAVE STATUS revealed something similar to this:


  [Read more...]
Making Deletions Fast, by Avoiding Disk Seeks
+1 Vote Up -2Vote Down

In my last post, I discussed how fractal tree data structures can be up to two orders of magnitude faster on deletions over B-trees. I focused on the deletions where the row entry is known (the storage engine API handler::delete_row), but I did not fully analyze how MySQL delete statements can be fast. In this post, I do. Here I show how one can use TokuDB, a storage engine that uses fractal tree data structures, to make MySQL deletions run fast.

Let’s take a step back and analyze the work needed to be done to execute a MySQL delete statement. Suppose we have the table:

create table foo (
	id auto_increment
	a int,
	b int,
	primary key (id)
)

Say we wish to perform the following operation that deletes 100,000 rows:

delete from
  [Read more...]
From Daily WTF: Death by Delete
+1 Vote Up -0Vote Down

The Daily WTF collects excellent tales from the real world. These days, the dismal dramatic sagas are often (at least in part) about mistakes involving databases; no surprise there, they’re so prolific…

Anyway, if you can learn from other people’s mistakes, that’s cheap and efficient education! I thought I’d share today’s edition with you: it’s called Death by Delete. Read and enjoy^H^H^H^H^Hlearn.

[MySQL] Deleting/Updating Rows Common To 2 Tables – Speed And Slave Lag Considerations
+1 Vote Up -0Vote Down

Introduction

A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

Solutions Suggested By Others

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
DELETE a FROM a INNER JOIN b on a.id=b.id;
DELETE FROM a WHERE id IN (SELECT id FROM b)

The Problem With  [Read more...]

Using a DELETE w/ a JOIN and LIMITing the number of rows deleted.
+0 Vote Up -0Vote Down
I've come up with some interesting workarounds for missing features using @session variables and I'd like to share one with you today: DELETE ... JOIN ... LIMIT N;

okay, so we've got two tables, one with many duplicates and one with no duplicates.
We want to join the tables using 'id', but only want to delete N duplicates from t1.
MySQL's DELETE does not include LIMIT support when JOIN is used, so we need to work around
that using some fancy footwork:


mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | xyz |
+----+------+
|


























  [Read more...]
Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.