Increase your MySQL productivity with Toad

Recently my favorite MySQL Swiss Army Knife has to be Toad for MySQL. Not only does it have a plethora of tools and interesting features, it is free. A few days back I’d to compare database schemas of different versions of a application to see if some table fields had changed between versions. Comparing a database schema containing above 200 tables can be a time consuming job if you do not have the right tools. Toad makes the work easier with its schema and data compare feature, which lets you easily compare schema and also the data from two different databases. You can even synchronize the two schemas or the data therein so both the databases contain the same schema and data.

Besides these it has a nice query builder, somewhat like the one in Microsoft Access. Toad also can manage your Amazon EC2 instances with its built-in tool, which I’ve yet to try. Other than these it has the other regular features – a database explorer, table builder, database diagram creator etc. Toad is now my favorite tool for managing MySQL data.

Read More

Sequence gaps in MySQL

Auto-increment sequences are a common way to define a primary key in MySQL. This types of artificial keys, known as surrogate keys are commonly used by developers to quickly construct a database table. A common reason developers use artificial keys is due to the fact that most do not take the time to search for a natural key in their model, especially SQL newbies. The question of whether we should use a surrogate key or a natural key is a debate we should leave to the database experts. The purpose of this post is to present some queries to find if a auto-increment sequence contains gaps.
(more…)

Read More

Multi column sequences in MySQL

One of the most common used attributes in MySQL is definitely AUTO_INCREMENT. This is quite helpful when one needs to generate unique identities for the table rows. By default when a AUTO_INCREMENT column is the only column in a index, whether PRIMARY KEY or UNIQUE, it generates a single monotonic sequence of numbers : 1,2,3,4,… etc. But for MyISAM storage engine it is possible to create complex sequences in a table containing an AUTO_INCREMENT column.
(more…)

Read More

Benchmarking WordPress SQL using FirePHP

firephp_thumbBuilding and running a WordPress site is a simple matter. WordPress is a considerably fast CMS system, until you start to add more and more plugins and one day you notice that Worpdress has started to slow down. It may be the case that SQL queries within some plugins are not optimized and are taking an increased amount of time executing them, this can considerably slow down your site. The first thing you can do to rectify the situation is to find out where exactly the bottleneck resides by analyzing the time each SQL query takes to executes. Some inquisitive people among you may also be interested in knowing in what sequence the WordPress SQL queries themselves are being run. Not that all bottlenecks occur due to unoptimized SQL, most are due to poor coding practices. Whatever the reason; the following post will show you how to look inside the SQL query execution of WordPress.
(more…)

Read More

Test data generation for MySQL

One of the most time consuming steps in developing a database based application is test data generation. Imagine you have created a shopping cart application for your client, containing dozens of tables and hundreds of fields. Everything is ready for testing, but the problem is your database is empty. You at least require a modicum of test data in the database to proceed with the testing. You can choose to manually enter a small set of random data into the database, but you lose a couple of precious days in the process. What you require is a data generator that automatically generates test data for your database.
(more…)

Read More

Adminer – a fast MySQL administration tool

adminer1Before you go ahead and think that you don’t need yet another MySQL management tool, I think you should at least take a quick look at this one. Adminer is a single file (yes! everything in a single php file) tool to manage all your MySQL related tasks. All you have to do is upload the adminer.php file to your server and voila! you are ready to go. No messing around with huge software’s to manage your database. Weighing only 170KB, its easy to upload to any server. A demo of the same is included on the Adminer site.
(more…)

Read More