Ferry Boender
Programmer, DevOpper, Open Source enthusiast.
Blog 
Feeds
Category: mysql
Very fast MySQL slave setup with zero downtime using rsync
Most online tutorials for setting up a slave replication database involve dumping and loading the database on the master with mysqldump. Unfortunately, loading data with mysqldump can be very slow. My friend Cris suggest a much faster method using rsync. Benefits The benefits of this method are: Very fast setup of a slave by avoiding […]
my_indexr: Script to drop and recreate MySQL indexes
As can be read in this article, I was in need of a method to quickly drop all indexes (except primary keys) from a MySQL database. After googling around a bit and being astonished that apparently no-one had written such a thing yet, I wrote the script that can be seen in that article. Unfortunately, […]
Increasing performance of bulk updates of large tables in MySQL
I recently had to perform some bulk updates on semi-large tables (3 to 7 million rows) in MySQL. I ran into various problems that negatively affected the performance on these updates. In this blog post I will outline the problems I ran into and how I solved them. Eventually I managed to increase the performance […]
Comment your MySQL schema
Many people may not now, but you can comment your MySQL schema: SQL: good comments conventions
Maatkit: Tools for MySQL
Maatkit is a suite of command-line tools for MySQL. It contains some rather nifty things for query analyses, replication, and other stuff. Some of the more interesting highlights: mk-deadlock-loggerExtract and log MySQL deadlock information. mk-log-playerSplit and play MySQL slow logs. mk-error-logFind new and different MySQL error log entries. mk-index-usageRead queries from a log and analyze […]
SQuirreL SQL database browser
I finally found a decent replacement for the MySQLcc database browser: SQuirreL SQL SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc It’s Java, so it’s slow, but it does everything I want, and […]
Filling gaps in data when using aggregates
SQL’s aggregate functions (COUNT, MIN, MAX, etc) in combination with GROUP BY is great for generating statistics from a database. It’s quite easy to retrieve a count of the rows in a database grouped by week number or month. When there are no data points in the table for a particular week or month, however, […]
MySQL WEEK() function weirdness
The other day I had to gather some statistics from a database, and the statistics needed to be grouped and filtered by weeknumbers. I ran into something a bit unexpected. Let’s say we have the follow table definition: CREATE TABLE test ( start DATETIME NOT NULL ); We insert the following dates for testing purposes: […]
MyQryReplayer
I’ve written a tool called MyQryReplayer: MyQryReplayer is a tool which can read the MySQL query log and replay an entire session’s worth of queries against a database (SELECT queries only by default). While doing so, it records the time each query took to run, and any queries that failed including their error messages. MyQryReplayer […]
SQL: Remove duplicate rows
In the post SQL: Find duplicate rows I explained how you can find duplicate rows in a table. However, if you want to delete them, you’ll run into a little problem as you have to delete all the duplicate rows except for one. Lots of solutions are floating around on the Internet, but most either […]
Search this blog:
The text of all posts on this blog, unless specificly mentioned otherwise, are licensed under this license.