MariaDB / MySQL Tricks

Replication

Stop replication and remove online replication information:

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status;
Empty set (0.001 sec)

Functions

Display epoch values:

MariaDB [sympa]]> select from_unixtime(mydate);

Queries across tables

Update field in products table with a sum from related table reviews:

update products P inner join
(select sku, count(sku) as rating_count from reviews where public is TRUE group by sku) I
on P.sku = I.sku SET
P.rating_count = I.rating_count

Table information

Columns with DESCRIBE

Filter columns of a table, e.g. show all columns ending in Datum:

mysql> describe Artikel '%Datum';
+--------------------+------+------+-----+---------+-------+
| Field              | Type | Null | Key | Default | Extra |
+--------------------+------+------+-----+---------+-------+
| Erfassungsdatum    | date | YES  |     | NULL    |       |
| LieferbarNeinDatum | date | YES  |     | NULL    |       |
| LieferbarDatum     | date | YES  |     | NULL    |       |
| Abschreibungsdatum | date | YES  |     | NULL    |       |
+--------------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Indexes

show indexes from transactions;

Global Information

Size of the databases in Megabytes:

SELECT table_schema AS "Database",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
  FROM information_schema.TABLES
  GROUP BY table_schema;

Size of the tables in a specific database (e.g. sympa):

SELECT table_name AS `Table`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
  FROM information_schema.TABLES  where table_schema = 'sympa'
  ORDER BY (data_length + index_length) DESC;

`

Optimization

Add indexes for joins

With EXPLAIN you can see which indices are used for the join. For large tables include in a join, you may see a huge performance gain.

Hints

https://github.com/major/MySQLTuner-perl#mysqltuner is a nice script that offers you performance and stability recommendations.