Peter Zaitsev’s blog entry on Duplicate indexes and redundant indexes certainly made a bit of a stir! It has already led to a new (mini) project by Baron Schwartz, the Duplicate index/foreign key finder which is publicly available on MySQLForge as a perl script. Daniel Schneller has entered the arena as well, devoting an entire blog to his own java implementation to tackle this and other index trouble.

I figured it would be fun to add a pure SQL solution for the problem. This is not too hard to implement with the STATISTICS system view found in the MySQL information_schema database. This solution is limited in that it will only work for MySQL 5.0 (and higher). On the other hand, it has the advantage that it does not require any external programming languages or runtime environments.

The solution I’m presenting here also takes the uniqueness of indexes into account, something that - to the best of my knowledge - has not been discussed by the other authors (although I believe that Daniel Schneller’s tool does take uniqueness into account).

Those that don’t feel like reading the article, hey - you decide. The code is available on MySQLForge as a snippet. The article just explains a little bit of the background of the problem, and explores some techniques to solve it using a pure SQL solution. It finishes with a detailed explanation of the actual query, which you may or may not find interesting to read.

With the use of MySQL in the enterprise applications and Web 2.0 applications, Backup and recovery of MySQL databases has become one of the important tasks for a database administrator. There are lots of methods to do application consistent MySQL database backups. The backup method used depends on MySQL configuration as well as the MySQL storage engines used by the MySQL database. These backup methods mostly deal with full
backup of the database.

MySQL maintains binary logs for supporting MySQL server replication. These binary logs can be used to selectively recover MySQL databases. MySQL binary logs contain all database events (SQL statements) that modify data or could have modified data. These binary logs can be used as incremental backups
of the MySQL server.

MySQL server does not create binary logs by default. It has to be enabled as a command line option to MySQL server. Enabling MySQL binary logging has minimal impact on the MySQL database application performance. MySQL provides mysqlbinlog tool to process binary logs. This tool can be used for selectively restoring database events based on when it occurred or position in the binary log.

Zmanda Recovery Manager(ZRM) for MySQL , a comprehensive MySQL backup and recovery manager, provides a mechanism to list the database events, time when the event occurred and the position in the binary log. This information can be used for selective recovery of database to any point in time between backups. I’m one of the ZRM for MySQL project developers. This is an open source project (GPL).

Selective recovery of databases can also be used for recovery from operator errors such as execution of DROP TABLE or DROP DATABASE statements or intentional sabotage of the database by an user. Of course, without regular backups, it is not possible to do selective recovery. It is also important to do restore audits of the backup images so that the backups are available when things go wrong.

Hi All,

My name is Roland Bouman, and I’m a certification developer for MySQL AB. This is my first post on the O’Reilly database weblog, and I figured it would be nice to start with a technical article about MySQL cursors, a subject I have written about before on my blogger weblog.

The first part of this article explains why cursors are usually unnecessary.
A few common problems with cursors are briefly discussed.
Also, typical stored procedure pattern is described that uses a cursor,
and a demonstration is given that shows how it can be refactored
to an equivalent procedure that uses a single SQL statement instead.

In the second part of this article,
the negative performance implications of using cursors are illustrated with a few benchmarks,
and the cases where a cursor might be useful after all are briefly discussed.