MySQL zerofill and lpad: whipping your digits into shape

I’m working with a large dataset. One of the columns is set as an INT(8) but not all of the values are 8 digits. I need to run a query that will sum values and group by the first two digits in that INT(8) column. This presents a potential problem since it may end up grouping incorrectly since I need everything to be grouped based off of 8-digit numbers. How do you make these smaller numbers into 8-digits?

Simple. There are two ways.

  1. Alter the table itself using the ZEROFILL in MySQL. This will add 0s to the left of your values up to the max number of digits defined for that column:
    • ALTER TABLE [table name] MODIFY COLUMN [column name] INT(x) ZEROFILL UNSIGNED;
    • Where table name = your table name, column name = the desired column to pad, and x is the number of digits allowed for that column
  2. Pad the values using lpad() in your query itself, without altering your table:
    • select lpad(column name,x,'0') from table name;
    • Where table name = your table name, column name = desired column to pd, and x = the number of digits to allow.
    • If your value in the DB is 332, your result will be 0332. If your value is 9, the result will be 0009.

Hopefully this will help someone facing the same problem I was. It’s also good to know that there are multiple ways of doing things.

Tuning MySQL for performance with Ubuntu 11.04 and MySQLTuner

One of my current projects requires extremely heavy use of very large data sets housed in sever MySQL databases and spread across several servers. This data feeds various web apps, business processes, and fulfills many requests. It is data that needs to be highly available at all times and return queries in the shortest amount of time possible. There are several database tables with upwards of 15 million records and in some instances these need to be joined to other tables, used in calculations, etc.

I need results now, not 48 hours from now. Performance is essential but hard to come by with legacy servers made out of repurposed machines. In order to see where the bottlenecks are and try to make the best of the situation I decided to do some searching. We’re running Ubuntu 11.04 servers for this purpose and I needed something that would give me a run down of what’s causing performance issues on each server.

That’s when I found mysqltuner. This handy little guy is a Perl script that looks at your my.cnf and other MySQL installation data and makes recommendations about how to improve performance based on your past usage.

It’s easy to install in Ubuntu server 11.04:

sudo apt-get install mysqltuner

To run it:

sudo mysqltuner

Then simply enter your administratrive user and password and you’ll get a nice little printout like so:

image[2]

This will give you a good set of recommendations about which settings to tweak. From here you can make tweaks, restart MySQL and run the script again to see where you stand.

Hope this will help someone in the same situation!