Gary Pendergast

Category: MySQL

  • JOIN and comma precedence

    Here’s a little something that might trip you up occasionally. Have a look at this test scenario: USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; CREATE TABLE a ( a INT ); CREATE TABLE b ( b INT ); CREATE TABLE c ( c INT );…

  • Tools of a Support Engineer

    So, you’ve emailed MySQL Support, they’re working on the problem you’re having. How are they working? What tools do they use? Well, here’s my list: IRC – All MySQL Support Engineers work on IRC, it’s our main communication medium. While you’re only getting emails from one Engineer, it’s quite likely they’re consulting with several others…

  • Backing up permissions for individual databases

    Sometimes, you want to backup individual databases in MySQL to move to a different server. This part is easy using mysqldump: shell> mysqldump -u root -p –databases db1 db2 … > backup.sql The problem is, what happens when you want to backup the permissions associated with these databases? Well, here are a few queries to…

  • A Brief Introduction to MySQL Performance Tuning

    Here are some common performance tuning concepts that I frequently run into. Please note that this really is only a basic introduction to performance tuning. For more in-depth tuning, it strongly depends on your systems, data and usage. Server Variables For tuning InnoDB performance, your primary variable is innodb_buffer_pool_size. This is the chunk of memory…

  • Don’t Quote Your Numbers

    It’s a fairly simple rule, and something that should be obeyed for your health and sanity. There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this…

  • The \G modifier in the MySQL command line client

    A little publicized, but exceedingly useful feature of the MySQL command line client is the \G modifier. It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’. For example, checking the master status: mysql> SHOW MASTER…