Drawing Graphs with MySQL

Drawing what with what???!?

Yes you read correctly. Let's suppose we got the following table layout in MySql:

+-------------+--------------+------+-----+---------+----------------+
| Fiel        | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| createTime  | DateTime     | YES  |     | NULL    |                |
| dur         | integer      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

It's a very basic logging table that simply stores generic events, its creation time and the time this event took to run in milliseconds. Lets query that table:

SELECT creationTime, executionTime FROM myLogTable

+---------------------+---------------+
| creationTime        | dur           |
+---------------------+---------------+
| 2013-11-23 13:27:00 |            10 |
| 2013-11-23 13:27:08 |            11 |
| 2013-11-23 13:27:11 |             9 |
| 2013-11-23 13:27:18 |            13 |
| 2013-11-23 13:27:20 |            13 |
| 2013-11-23 13:27:21 |            25 |
| 2013-11-23 13:27:30 |             9 |
| 2013-11-23 13:27:35 |            30 |
| 2013-11-23 13:27:37 |            15 |
| 2013-11-23 13:27:45 |             8 |
+---------------------+---------------+

Obviously this data is a little hard to read, wouldn't it by nice to have some kind of a visualisation?
Let's try this query:

SELECT id, creationTime, executionTime, repeat('|', executionTime) as graph FROM myLogTable

+---------------------+-----+--------------------------------+
| creationTime        | dur | graph                          |
+---------------------+-----+--------------------------------+
| 2013-11-23 13:27:00 |  10 | ||||||||||                     |
| 2013-11-23 13:27:08 |  11 | |||||||||||                    |
| 2013-11-23 13:27:11 |   9 | |||||||||                      |
| 2013-11-23 13:27:18 |  13 | |||||||||||||                  |
| 2013-11-23 13:27:20 |  13 | |||||||||||||                  |
| 2013-11-23 13:27:21 |  11 | |||||||||||                    |
| 2013-11-23 13:27:30 |   9 | |||||||||                      |
| 2013-11-23 13:27:35 |  30 | |||||||||||||||||||||||||||||| |
| 2013-11-23 13:27:37 |  15 | |||||||||||||||                |
| 2013-11-23 13:27:45 |   8 | ||||||||                       |
+---------------------+-----+--------------------------------+

Now we got a graphic representation of the value that shows us anomaly on the first glance.

Sorting Data in a MySQL query before grouping

In some cases you have the problem that you need to sort your data before grouping it in a single SQL query. Normally it's impossible to do this in a single SQL SELECT statement, because the result is first grouped, then sorted.

Given a database table which contain images in different resolutions for products like this:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| article_id | int(11)      | YES  |     | NULL    |                |
| width      | int(11)      | YES  |     | NULL    |                |
| height     | int(11)      | YES  |     | NULL    |                |
| filename   | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Imagine to select only the biggest image for a couple of article ids you could try something like this:

SELECT article_id, width, height, filename
FROM article_images
WHERE article_id IN(23, 42)
GROUP by article_id
ORDER by width*height

But you'll only get the first entry for a given article_id, not for the biggest resolution.

So let's try another approach:

SELECT article_id, width, height, filename
FROM (SELECT article_id, width, height, filename
      FROM article_images
      WHERE article_id IN(23, 42)
      ORDER by width*height
) as ghoti
GROUP by article_id

This query first selects all images in all resolutions for the given article_ids and sorts it by resolution, copying it into a temporary table named ghoti. The query around this subselect then groups the data by article_id. Because we have sorted by resolution before it results in the biggest image for a given article_id.

ghoti is our name used internally for that kind of SELECT, because we need it sometimes to transfer less data to our PHP scripts and crunch data on the database server. What ghoti means is described in this wikipedia article.