MySQL – CAST + SUBSTR vs. FLOOR + MOD

MySQL

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two different things:

SELECT x hour, … FROM table WHERE … GROUP BY x …

  1. x = SUBSTR(CAST(time AS CHAR(14)), 9, 2)
  2. x = MOD(FLOOR(time / 10000), 100)

I built a quick benchmark (did 1000 querys with each version) and both of them seem to be equally quick so I'm really interested in what database designers would advise me to do?

Best Answer

Perhaps as a third alternative you may want to try

X = RIGHT(CAST(time as CHAR(14)),2);

Here is a fourth alternative requiring some project management decisions: Instead of storing date('YmdHis')) you should store date('U')). The U stands for seconds since Jan 1, 1970. Then, your formula would be simpler

X = MOD(time,60);

If you must keep what you have, use the BENCHMARK function

SET @dt=20130416152057;
SELECT SUBSTR(CAST(@dt AS CHAR(14)),9,2);
SELECT MOD(FLOOR(@dt / 10000), 100);
SET @iterations=1000000;
SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
SET @iterations=10000000;
SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));

I tried it on a random DB Server 1 millions times and then 10 million times

mysql> SET @dt=20130416152057;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SUBSTR(CAST(@dt AS CHAR(14)),9,2);
+-----------------------------------+
| SUBSTR(CAST(@dt AS CHAR(14)),9,2) |
+-----------------------------------+
| 15                                |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MOD(FLOOR(@dt / 10000), 100);
+------------------------------+
| MOD(FLOOR(@dt / 10000), 100) |
+------------------------------+
|                           15 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SET @iterations=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
+----------------------------------------------------------+
| BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2)) |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (0.15 sec)

mysql> SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
+-----------------------------------------------------+
| BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100)) |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (0.40 sec)

mysql> SET @iterations=10000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2));
+----------------------------------------------------------+
| BENCHMARK(@iterations,SUBSTR(CAST(@dt AS CHAR(14)),9,2)) |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (1.50 sec)

mysql> SELECT BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100));
+-----------------------------------------------------+
| BENCHMARK(@iterations,MOD(FLOOR(@dt / 10000), 100)) |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+
1 row in set (4.02 sec)

mysql>

On the particular hardware I ran this on, your first expression is about 2.67 times faster.

Give it a Try !!!

Related Question