If you ever had to do query performance troubleshooting with MariaDB, you should be
familiar with MariaDB’s ANALYZE for statements feature. It does what EXPLAIN ANALYZE does in some other database systems: ANALYZE query runs the query and produces EXPLAIN output, amended with the data from the query execution:
FROM orders, customer
customer.c_custkey = orders.o_custkey AND
customer.c_acctbal < 0 AND
orders.o_totalprice > 200*1000
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
| 1 | SIMPLE | customer | ALL | PRIMARY,…
The MariaDB 10.9 preview releases introduce a MySQL compatibility syntax extension, range notation. The ‘last’ keyword was added in JSON path expression, as requested in MDEV-22224 and MDEV-27911. Additionally, negative indexes are now also supported. Range notation, implemented using the ‘to’ keyword, and the ‘last’ keyword, is basically an array element selector.
[M to N] selects a range of elements starting from index M to N.
[last-N] / [last] selects the Nth from last element, and last element, respectively.
If you are using table partitioning, you have likely heard of the ALTER TABLE … EXCHANGE PARTITION … WITH TABLE … command. It existed in MariaDB since forever. But if you check the manual (any manual) or search the web, you will see that almost the only use case of it is converting a partition to a standalone non-partitoned table, or converting a standalone non-partitoned table into a partition.
And the usage was designed back then to be anything but obvious. To convert a partition to a table you need first to create an empty table with the same structure as a partition, then you exchange it with a partition, and then you drop the empty partition.
Sometimes there is a need to combine data from different columns into one string. For example,
SELECT CONCAT(first_name, ‘ ‘, last_name) FROM employees;
This doesn’t look too bad, but can quickly get out of hand, if you need to do something more complex than that. For example, let’s say, we also need to mention the salary here:
SELECT CONCAT(first_name, ‘ ‘, last_name, ‘ -‘, CAST(FORMAT(salary, 0) AS VARCHAR(10)), ‘ ‘, currency) FROM employees;