When I wrote my first article on algorithms to compare and synchronize data between MySQL tables, Webyog's Rohit Nadhani left a comment on the article mentioning the SQLyog Job Agent, which has a similar function. Although I have been developing MySQL Table Sync essentially in isolation, I have been meaning to give SQLyog Job Agent a try. I recently did so, and then followed that up with an email conversation with Rohit. This article is about my experience using the SQLyog Job Agent from the command line, some thoughts on the algorithm as best I can deduce it, and benchmark results against MySQL Table Sync.

I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to "SELECT count(*) FROM products" the best way, is to have a separated table
that stores the number of products. If you're inserting a row increment the counter, if you're deleting a row, decrement it.

Here is some example:
CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you're adding a new product to the products table:

PLAIN TEXT SQL:

1. UPDATE counter SET …

I wrote an article late last week about benchmark results for the two table-synchronization algorithms I've been implementing for the MySQL Table Sync tool. I've spent some time developing a test suite for the tool, and learned some really interesting things about the general problem of synchronizing tables.

I've been working on how to efficiently compare and synchronize data between two tables on different MySQL servers. I've also been working on a tool, sort of like rsync for database tables, which implements both algorithms. I profiled it to see how well the comparison algorithms work on real data. This article is about the results.

MySQL Table Maintainer is a new utility to help you run table maintenance commands (ANALYZE, CHECK, OPTIMIZE, REPAIR) on your MySQL tables. It's part of the MySQL toolkit.

I've just released updates to all the tools in the MySQL Toolkit. The biggest change I made to most packages is using DBD to read MySQL's option files, though some packages got more significant updates.

MySQL Toolkit's Show Grants tool can now separate grants into individual statements, convert them into REVOKE statements, and help you remove users from a server, even if your server doesn't support DROP USER.

MySQL Table Sync efficiently finds and resolves data (not structural) differences between two MySQL tables, which may be on different servers. It offers two search algorithms to find the rows that differ, and several methods to bring the destination table into sync with the source.

MySQL Toolkit's Show Grants tool makes it easy to extract grants from a MySQL server in canonical form. You can use it to replicate grants between servers, diff grants, and avoid spurious changesets in version control systems.

A while ago I wrote about how to select the first, minimum, maximum or least row per group in SQL. This article shows how to solve this problem without subqueries.