2 posts on MySQL

This might seem elementary to those of you that are DBAs or something similar, but it was fascinating to find out (not to mention it greatly helped what I had to do), so I decided to post it, in case it helps someone else too. A few moments ago I found out that whereas a query along the lines of…

SELECT title, COUNT(1) as replies
FROM post INNER JOIN thread USING(threadid)
WHERE **UNIX\_TIMESTAMP(NOW()) - post.dateline < 86400**
GROUP BY threadid
ORDER BY replies DESC
LIMIT 5

took a whopping ~10 seconds on a post table of around 2,000,000 rows and a thread table of around 40,000 rows, the following:

SELECT title, COUNT(1) as replies
FROM post INNER JOIN thread USING(threadid)
WHERE **post.dateline > UNIX\_TIMESTAMP(NOW()) - 86400**
GROUP BY threadid
ORDER BY replies DESC
LIMIT 5

took a mere 0.03 seconds!

Probably, MySQL wasn’t able to utilize the B+ tree index of the dateline column in the first query, whereas in the second, things were a bit more obvious to it. This can also be observed by examining the information about the execution plan that EXPLAIN provides:

mysql> explain select t.threadid, t.title, count(1) as replies from vb3\_post as p inner join vb3\_thread as t using(threadid) where unix\_timestamp(now()) - p.dateline < 86400 group by p.threadid order by replies desc limit 5;
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
| id | select\_type | table | type | possible\_keys | key      | key\_len | re         | rows  | Extra                           |
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
|  1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL     | NULL    | NULL       | 39859 | Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref  | threadid      | threadid | 4       | t.threadid |    49 | Using where                     |
+----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+
2 rows in set (0.01 sec)
```
mysql&gt; explain select t.threadid, t.title, count(1) as replies from vb3\_post as p inner join vb3\_thread as t using(threadid) where p.dateline &gt; UNIX\_TIMESTAMP(NOW()) - 86400 group by p.threadid order by replies desc limit 5;
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
| id | select\_type | table | type   | possible\_keys     | key      | key\_len | ref        | rows | Extra                                        |
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | range  | threadid,dateline | dateline | 4       | NULL       | 1171 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq\_ref | PRIMARY           | PRIMARY  | 4       | p.threadid |    1 |                                              |
+----+-------------+-------+--------+-------------------+----------+---------+------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

So, don’t rest assured that MySQL will use your indexes every time it should. It seems that sometimes you have to explicitly point it out.

In the past, I used MySQL for any of my DBMS needs. It wasn’t really an informed decision based on solid facts, actually I had never really given it any thought. It was what most developers used, it was what vBulletin used (one of the main projects of my company is based on vBulletin), it was what most hosts had pre-installed, in other words, it was the popular choice and I went with the crowd.

Unlike most decisions taken that way, this one turned out to be correct (so far at least). In the university where I study (yeah, I do that too occasionally :P ), there is a great and extremely useful class on Database Systems offered in my semester. The only drawback is that it’s done on MS SQL Server. Consequently, I had to work with it quite a lot, and my conclusion was that MySQL is far superior (mostly syntax-wise as I don’t have the deep knowledge required to judge them fairly for other things, so don’t expect a deep analysis about performance or security - as far as I’m concerned, they are equally good at those). Here are a few reasons:

1. No ENUM datatype. Yeah, of course I can define a column with a char/varchar type and add a constraint to only allow for particular strings, but this kinda defeats the purpose of memory saving that the ENUM datatype in MySQL offers.
2. No INSERT IGNORE. Instead you have to go through hell to simulate that in MS SQL Server.
3. I hate it that I can’t use “USING(columnlabel)” in a JOIN query and I have to use “ON(table1.columnlabel = table2.colmnlabel)” all the time. Yeah, I know that the first one isn’t standard, but it’s shorter, cleaner, more elegant, and …you can still use “ON(…)” if you don’t like it. Having more options is never bad, is it?
4. With MySQL you may insert multiple rows at once elegantly (“INSERT INTO tablename (…), (…), …”), without using the “INSERT INTO tablename SELECT (…) UNION ALL SELECT (…) UNION ALL …” hack. Moreover, the elegant MySQL way also happens to be the standard, a standard that SQL Server doesn’t follow.
5. Triggers can only run per statement, and not per row. This isn’t really important, since for most cases, it’s more efficient to define a per statement trigger anyway, but it doesn’t do any harm to have an extra option, does it?
6. Paging is dead-easy on MySQL: SELECT * FROM foo LIMIT 10,20 . With MS SQL Server you have to jump through hoops to do the same thing, especially if your query is not trivial.
7. In MySQL, when you want to convert an integer to a hex string, you just call HEX(). In SQL Server you have to call an undocumented function and do some string manipulation to do the exact same thing.
8. MySQL runs on every platform, whereas with MS SQL Server you’re stuck with Windows.
9. Last but not least, MySQL is free (and when it’s not free, it’s at least cheap) and opensource :-)