This is an old revision of the document!
option | description |
---|---|
noatime | access timestamps on the filesystem are not updated. |
commit=60 | The sync interval for data and metadata can be increased by providing a higher time delay to the commit option. |
barrier=0/nobarrier | Careful! |
shell> mysqldump --databases db1 db2 db3 > dump.sql
mysqldump --all-databases > dump.sql
You can query with the `@>` operator on `metadata`. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on `metadata` column.
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';
The `→>` operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on `metadata→>'country'`.
SELECT * FROM users WHERE metadata->>'country' = 'Peru';
Once again, the `→>` operator gets a JSON object field as text. Just use directly it in the `SELECT`.
SELECT metadata->>'country' FROM users;
You can use the `→>` operator with the classic operator you use on text: `=`,`<>`,`IS NULL`, etc. Do not forget to index `metadata→>'country'` with a B-tree index.
SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;
You can use both `@>` or `→>`, just like for first level attribute. Add an index according to your choice.
SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';
Remembering `@>` operator checks containment in a JSONB column, you can query on an array like `{“x”: [“a”, “b”, “c”]“}` by just passing `{“x”:[“a”]}` to the `WHERE` clause:
SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';
Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values.
SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');
Use `UPDATE … SET` as usual and pass the whole object as JSON.
UPDATE users SET metadata = '{"country": "India"}';
Use the `||` operator to concatenate the actual data with the new data. It will update or insert the value.
UPDATE users SET metadata = metadata || '{"country": "Egypt"}';
The operator - removes a key from an object.
UPDATE users SET metadata = metadata - 'country';
MariaDB [(none)]> show global variables like 'slow%log%'; +---------------------+-------------------------+ | Variable_name | Value | +---------------------+-------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/slow.log | +---------------------+-------------------------+
MariaDB [(none)]> set global slow_query_log_file='/var/log/mysql/slow.log'; Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> set global slow_query_log=ON; Query OK, 0 rows affected (0.001 sec)
https://mariadb.com/kb/en/slow-query-log-overview/#enabling-the-slow-query-log https://mariadb.com/resources/blog/what-is-the-best-linux-filesystem-for-mariadb/ https://shatteredsilicon.net/blog/2020/06/05/mysql-mariadb-innodb-on-zfs/