mysql

Taken from http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB, BDB, and NDBCLUSTER.

For InnoDB and BDB tables, MySQL only uses table locking if you explicitly lock the table with LOCK TABLES. For these storage engines, we recommend that you not use LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

The most efficient way to remove duplicate rows from a table is by using what we database freaks call 'CTAS'. It is an exotic acronym for the SQL statement 'CREATE TABLE AS'. Imagine table T1 contains duplicate rows and we want to create table T2 as a non-duplicate version of T1. The syntax is as follows:

CREATE TABLE T1 AS SELECT DISTINCT * FROM T2;

In Oracle this will generate lots of temp information so be sure you provide an ample temp tablespace!

Suppose we have the following table which consists of two columns - a user and his favorite album.

mysql> select * from albums_votes;
+-----------+----------+
| user      | album_id |
+-----------+----------+
|      1000 |        2 |
|      2000 |        3 |
|      3000 |        1 |
|      4000 |        3 |
|      5000 |        3 |
|      6000 |        3 |
| 531514255 |        1 |
| 686711159 |        2 |
| 618496371 |        2 |
| 705891065 |        2 |
+-----------+----------+
10 rows in set (0.00 sec)