Benchmark Query 4x Faster on MySQL 5.5

[Brian Boatright]

Running this query is almost four times faster on MySQL than the same version of MariaDB.

SELECT BENCHMARK(10000000,DATE_SUB(FROM_UNIXTIME(RAND() * 2147483648), INTERVAL (FLOOR(1 + RAND() * 365)) DAY));

Results on Workstation Win7 x64 16gb i7 3ghz
- MariaDB 5.3.3 - 17.05 secs
- MySQL 5.5.17 - 4.09 secs

Results on Server Win2k8 R2 x64 8gb Xeon 1270 3.4ghz
- MariaDB 5.5.24 - 22.09 secs
- MySQL 5.5.24 - 5.90 secs

Results on older server running Win2k3 x32 4gb Xeon 5050 3ghz
- MySQL 5.0.27 - 22.00 secs

Is there any reason this query would be running so much slower on MariaDB vs MySQL?

[Elena Stepanova (elenst)]

I've got similar difference on my machine on the latest releases (all release tarball packages):

MariaDB 5.5.24: ~25 sec
MariaDB 5.3.7: ~25 sec
MariaDB 5.2.12: ~8 sec
MySQL 5.5.25: ~8 sec
MySQL 5.6.5: ~20 sec

[Vladislav Vaintroub (wlad-montyprogram)]

Was there a real-life workload that goes much slower with MariaDB 5.5 or was this merely out-of-curiosity type of question?

If this is out-of-curiousity, the explanation is this:

MariaDB implemented microseconds precision for temporal datatypes. In the past, internal representation of temporal datatypes was 64 bit integer. Now, it is decimal. Decimals are slower than integers in many scenarios .

Now, there are still some optimizations to speed up datetimes and avoid decimals in different scenarios. For example, FROM_UNIXTIME understands that its parameter is integer and does not do decimal arithmetic in this case. In your example, FROM_UNIXTIME gets a double parameter and thus needs to be converted into decimal.

Thus, you can speed up your query by roughly 50%, if you rewrite it as

BENCHMARK(10000000,DATE_SUB(FROM_UNIXTIME(FLOOR(RAND() * 2147483648)), INTERVAL (FLOOR(1 + RAND() * 365)) DAY))

Note the FROM_UNIXTIME(FLOOR(...)), FLOOR was not there in original query.

[Brian Boatright (brian2k1)]

It wasn't purely out-of-curiosity. It was to confirm the overall performance that MariaDB has vs MySQL. I'm new to MariaDB and not a DB engineer or even a full time DBA.

With your new query forcing a integer result from RAND it is still much slower than MySQL but is faster overall.

Results for new query from my workstation:
- MariaDB 5.3.3 - 15.00 secs
- MySQL 5.5.17 4.04 secs

I would think as a general rule that any query would run at least the same speed if not faster. Even with the FLOOR RAND it it still slower by a significant amount.