I experimented with this and found something very interesting.
Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino.
If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put what I put in my my.cnf at the end.
Could you test this for your 3 scenarios (CREATE, TRUNCATE & DELETE) running your server using the new my.cnf. If you obtain similar results to mine, then we can investigate further.
Strangely, as far as I can see, the client reports a time of approx 0.3 seconds for me for all of my experiments - but the results from the profiling seem very different. Still puzzling through this one.
Below are my results just for the CREATE table statement before and after enabling the performance schema.
Before enabling P_S.
mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000290 |
| checking permissions | 0.000021 |
| Opening tables | 0.000141 |
| creating table | 0.365769 | <<====
| After create | 0.000037 |
| query end | 0.000023 |
| closing tables | 0.000018 |
| freeing items | 0.000114 |
| cleaning up | 0.000030 |
+----------------------+----------+
9 rows in set, 1 warning (0.00 sec)
After enabling P_S
mysql> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000188 |
| checking permissions | 0.000131 |
| query end | 0.000015 |
| closing tables | 0.000020 |
| freeing items | 0.000068 |
| cleaning up | 0.000052 |
+----------------------+----------+
6 rows in set, 1 warning (0.00 sec)
MariaDB
Before enabling P_S
Query OK, 0 rows affected (0.33 sec)
MariaDB [test]> show profile for CREATE TABLE cache_bootstrap;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000321 |
| checking permissions | 0.000024 |
| Opening tables | 0.000073 |
| After opening tables | 0.000017 |
| System lock | 0.000011 |
| Table lock | 0.000019 |
| After opening tables | 0.000056 |
| creating table | 0.328769 | <<======
| After create | 0.000043 |
| query end | 0.000026 |
| closing tables | 0.000022 |
| freeing items | 0.000043 |
| updating status | 0.000050 |
| cleaning up | 0.000039 |
+----------------------+----------+
14 rows in set (0.00 sec)
After enabling P_S on MariaDB
MariaDB [test]> show profile for CREATE TABLE cache_bootstrap
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000311 |
| checking permissions | 0.000024 |
| Opening tables | 0.000044 |
| After opening tables | 0.000038 |
| System lock | 0.000014 |
| Table lock | 0.000012 |
| After opening tables | 0.000347 |
| After create | 0.000015 |
| query end | 0.000014 |
| closing tables | 0.000021 |
| freeing items | 0.000014 |
| updating status | 0.000026 |
| cleaning up | 0.000034 |
+----------------------+----------+
13 rows in set (0.00 sec)
To be added in the [mysqld] section of my.cnf
[mysqld]
performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON
Best Answer
MariaDB-10.2.2 InnoDB merged from 5.7.14
MariaDB-10.2.35 Merge new release of InnoDB 5.7.32
Lots of miscellany features have been merged at random times.
MySQL 8.0 and MariaDB 10.5 are quite different. Still, you may not notice any differences unless you reach into rarely-used features.
The two play leapfrog. For example.
MariaDB had CTEs and Windowing functions in 10.2, long before they were added in MySQL 8.0.
On the other hand, MySQL led the way with JSON and some functions (eg ST_distance_sphere).
Better links:
https://mariadb.com/kb/en/function-differences-between-mariadb-and-mysql/
https://mariadb.com/kb/en/mariadb-vs-mysql-features/
And there maybe more. Note: it is in MariaDB's best interest to list the differences; MySQL prefers to mostly ignore the existence of MariaDB.
Errors, issues, bugs -- They look at each other's bug list, then add fixes as they see fit.
Syntax - Not much difference (except for CTEs, see above)
Features and behavior - MariaDB moved toward standards more aggressively than MySQL. For example, in a subquery with an
ORDER BY
, the ordering became ignored in MariaDB long before in MySQL.ONLY_FULL_GROUP_BY
is another point of difference. In both of these examples, the "new" version is "more correct", though incompatible.The Optimizers have diverged. I deal with performance a lot, but I would say only a few percent of queries show a noticeable difference between the products. And the difference could be in favor of either product.
If you are just starting, don't worry about which one you pick; you have a long learning curve before you get into the esoteric things I have listed.