A profiler for MySql

Allright, one things that really bugs me when I do random development in PHP/MySql is the lack of the fantastic SQL Server profiler which lets you monitor all queries coming in to the database.
I haven’t been looking too hard for one in MySql but after being stuck on some seriously annoying bugs in a project I decided to go find one.
And I did.
And it was costly…

If you have money, I’m sure the Jet Profiler for MySql is great, but I don’t and I’m not paying $499 for monitoring some queries…

There is a command line profiler that works great and that arrived in MySql version 5.0.37 and it’s quite easy to use, here’s an example:

mysql> SET profiling = 1;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT * FROM USER;
Empty SET (0.00 sec)
 
mysql> INSERT INTO USER (Username, Password) VALUES ('Superuser', 'Secret');
Query OK, 1 ROW affected (0.03 sec)
 
mysql> SELECT * FROM USER;
+----+-----------+----------+
| Id | Username  | Password |
+----+-----------+----------+
|  2 | Superuser | Secret   |
+----+-----------+----------+
1 ROW IN SET (0.00 sec)
 
mysql> SHOW profiles;
+----------+------------+-------------------------------------------------------
---------------+
| Query_ID | Duration   | Query
               |
+----------+------------+-------------------------------------------------------
---------------+
|        1 | 0.00032425 | SELECT * FROM USER
               |
|        2 | 0.03341050 | INSERT INTO USER (Username, Password) VALUES ('Superus
er', 'Secret') |
|        3 | 0.00022775 | SELECT * FROM USER
               |
+----------+------------+-------------------------------------------------------
---------------+
3 ROWS IN SET (0.00 sec)
 
mysql>

Very nice, this can help a lot when your running dynamic queries and want to see why they’re not working :)!
One thing though, the profiling works on a session basis, so different sessions cannot listen to the others queries, but this can be used inside whatever application you’re running in tests as well.

MySql: if-condition

Sometimes it can be handy to have a if-condition in a SQL query and MySql makes it quite easy. The syntax is quite easy to learn. Here’s an example:

Tabell `Person`:
Id Name Age Gender AddressId 
3	John Doe	50	Male	3
4	Jane Doe	30	Female	4
6	Jake Doe	35	Male	3
7	Jessica Doe	61	Female	4

If we want to fetch all name and prefix them “Mr” or “Mrs” then we could do this:

SELECT IF( Gender = 'Male', CONCAT( 'Mr', ' ', Name ) , CONCAT( 'Mrs', ' ', Name ) ) AS Name
FROM `Person`
WHERE 1
LIMIT 0 , 30

This will fetch all the names and prefix them with Mr if they are male and Mrs if they are female (I’m prudent enough to assume that the names of unmarried women are not stored in a database :))

Name 
Mr John Doe
Mrs Jane Doe
Mr Jake Doe
Mrs Jessica Doe