Squarebits Blog

We all use SELECT statements to retreive and analyze data in the database. You can improve your ability to quickly analyze data by formatting values into simple bar charts within your SELECT results. Here is an example…

Let’s say you have this data…

mysql> SELECT Name, Value FROM Month;
+-----------+---------+
| Name | Value |
+-----------+---------+
| January | 379.385 |
| February | 906.076 |
| March | 392.224 |
| April | 242.893 |
| May | 37.7936 |
| June | 460.288 |
| July | 188.06 |
| August | 559.437 |
| September | 233.005 |
| October | 486.714 |
| November | 734.555 |
| December | 212.632 |
+-----------+---------+
12 rows in set (0.00 sec)

You can generate a simple bar chart of the values by executing this command…

mysql> select Name, REPEAT("#", Value/20.0+1) Graph FROM Month;
+-----------+------------------------------------------------+
| Name | Graph |
+-----------+------------------------------------------------+
| January | #################### |
| February | ############################################## |
| March | ##################### |
| April | ############# |
| May | ### |
| June | ######################## |
| July | ########## |
| August | ############################# |
| September | ############# |
| October | ######################### |
| November | ###################################### |
| December | ############ |
+-----------+------------------------------------------------+
12 rows in set (0.00 sec)

Within the REPEAT() function, you’ll want to adjust the the constant value appropriately for the scale of your data. While this uses an ultra simple technique to repeat the # character to create the bar charts, this is still an incredibly useful means to improve your ability to quickly analyze the data.

With a single mysqldump command, you can export a subset of your MySQL database to an XML file.  This can save you considerable time in writing a script or application to selectively write out the appropriate data to XML.  Here is how you do this…

Let’s say you have this data…

mysql> select * from Month;
+---------+-----------+
| MonthID | Name      |
+---------+-----------+
|       1 | January   |
|       2 | February  |
|       3 | March     |
|       4 | April     |
|       5 | May       |
|       6 | June      |
|       7 | July      |
|       8 | August    |
|       9 | September |
|      10 | October   |
|      11 | November  |
|      12 | December  |
+---------+-----------+
12 rows in set (0.00 sec)

Now, let’s say you only want to export the months ending in "ber" to XML.  You can execute the following command to do this…

mysqldump -q -X -t -w "Name like '%ber'" mydatabase Month

In this command, the -q turns off buffering of the results (appropriate for large tables), the -X specifies XML output, the -t specifies to turn off writing table meta data, the -w specifies a WHERE clause to run (omit this switch to export all data), the mydatabase is the name of the database, and Month is the name of the table to export.

This results in the following…

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="realmetrics">
        <table_data name="Month">
        <row>
                <field name="MonthID">9</field>
                <field name="Name">September</field>
        </row>
        <row>
                <field name="MonthID">10</field>
                <field name="Name">October</field>
        </row>
        <row>
                <field name="MonthID">11</field>
                <field name="Name">November</field>
        </row>
        <row>
                <field name="MonthID">12</field>
                <field name="Name">December</field>
        </row>
        </table_data>
</database>
</mysqldump>

Now, let’s say you want to exclude the "MonthID" field from the results, you could do this by executing the following under Linux…

mysqldump -q -X -t -w "Name like '%ber'" mydatabase Month | fgrep -v "name=\"MonthID\""

This command results in…

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="realmetrics">
        <table_data name="Month">
        <row>
                <field name="Name">September</field>
        </row>
        <row>
                <field name="Name">October</field>
        </row>
        <row>
                <field name="Name">November</field>
        </row>
        <row>
                <field name="Name">December</field>
        </row>
        </table_data>
</database>
</mysqldump>

This example is simple but you might use this technique to export all of a customer’s data to XML if requested.

Recently, we ran a simple ALTER statement on a very large table that would normally run in about 2 hours but instead took 2 days to complete the alter (yes, 48 hours).  Here is how we fixed this problem so ALTERs would again run relatively quickly on our large tables.

Using "show processlist", we could see the ALTER had a status of "Repair by Key Cache" instead of the normal "Repair by Sorting".  After some research, we realized our indexes on this table had grown beyond the size that MySQL could use Repair by Sorting with our current configuration.

We did two things to fix this so that future ALTERs would be able to use the much faster "Repair by Sorting" instead of the dreaded "Repair by Key Cache"…

1. We increased the size of the myisam_max_sort_file_size parameter in MySQL.  This parameter should be larger than the maximum size of any key on the table multiplied by the number of rows in the table.
2. We changed the tmpdir parameter in MySQL from /tmp to /opt/tmp.  The size of our /tmp partition was also not large enough to handle repairing some of our large tables.

If you manage large MySQL database implementations, probably worth a bit of homework before you run your next alters to make sure that you have the myisam_max_sort_file_size parameter sized properly and you have enough space at the tmpdir parameter location to handle a "Repair by Sorting" versus "Repair by Key Cache".

With MySQL 5.x, you can create custom functions that can be invoked from other SQL statements.  Here is an example of creating a custom function called OverlapSeconds() that calculates how many seconds of overlap there are between any two time periods…

DELIMITER | ;
CREATE FUNCTION OverlapSeconds(period1Beg DATETIME, period1End DATETIME, period2Beg DATETIME, period2End DATETIME)
RETURNS INT LANGUAGE SQL DETERMINISTIC
BEGIN
    DECLARE time1Beg,time1End,time2Beg,time2End,result INT;
    SET time1Beg = UNIX_TIMESTAMP(period1Beg);
    SET time1End = UNIX_TIMESTAMP(period1End);
    SET time2Beg = UNIX_TIMESTAMP(period2Beg);
    SET time2End = UNIX_TIMESTAMP(period2End);
    IF time2End<=time1Beg THEN
        SET result = 0;
    ELSEIF time2Beg>=time1End THEN
        SET result = 0;
    ELSEIF time2Beg>=time1Beg AND time2End<=time1End THEN
        SET result = time2End-time2Beg;
    ELSEIF time1Beg>=time2Beg AND time1End<=time2End THEN
        SET result = time1End-time1Beg;
    ELSEIF time2Beg<=time1Beg AND time2End>=time1Beg THEN
        SET result = time2End-time1Beg;
    ELSEIF time2Beg<=time1End AND time2End>=time1End THEN
        SET result = time1End-time2Beg;
    END IF;
    RETURN result;
END|

DELIMITER ; |

You can then invoke this function like this…

mysql> SELECT OverlapSeconds(’2006-10-01 10:00′, ‘2006-10-01 10:10′, ‘2006-10-01 10:08′, ‘2006-10-01 10:18′) Overlap;
+———+
| Overlap |
+———+
|     120 |
+———+
1 row in set (0.00 sec)

So, the period from 2006-10-01 10:00 to 2006-10-01 10:10 overlaps with the period 2006-10-01 10:08 to 2006-10-01 10:18 by 120 seconds.