Sunday 12 January 2014

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.

No comments:

Post a Comment