Hidden Features of MySQL

Question

I've been working with Microsoft SQL Server with many years now but have only just recently started to use MySQL with my web applications, and I'm hungry for knowledge.

To continue with the long line of "hidden feature" questions, I would like to know any hidden or handy features of MySQL which will hopefully improve my knowledge of this open source database.

Answer 1

A command to find out who is doing what:

mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host            | db   | Command | Time | State                            | Info             |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
|  1 | root        | localhost:32893 | NULL | Sleep   |    0 |                                  | NULL             |
|  5 | system user |                 | NULL | Connect |   98 | Waiting for master to send event | NULL             |
|  6 | system user |                 | NULL | Connect | 5018 | Reading event from the relay log | NULL             |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec) 

And you can kill a process with:

mysql>kill 5 

Answer 2

During my benchmarks with large datasets and DATETIME fields, it's always slower to do this query:

SELECT * FROM mytable
WHERE date(date_colum) BETWEEN '2011-01-01' AND ''2011-03-03';

Than this approach:

SELECT * FROM mytable
WHERE date_column BETWEEN '2011-01-01 00:00:00' AND '2011-03-03 23:59:59'