select familienname,vorname,1_jahr_bezahlt,2_jahr_bezahlt,avastId from t_users u  inner join t_hosts h on (u.userId=h.userId) inner join t_avast a on (h.hostId=a.hostId);

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

SHOW CREATE VIEW my_view_name;

alter table t_hosts add mac_address varchar(17);

alter table t_hosts change mac_adress mac_address varchar(17);

explain t_hosts;

update t_hosts set hostname='motog-nadin' where hostId='4';

show grants for 'marko'@'marko-ThinkPad-T500.tuxnet.local';

revoke all privileges on `tuxnetlocal`.* from 'marko'@'marko-thinkpad-t500.tuxnet.local';

GRANT all privileges on `KMyMoney`.* TO 'marko'@'notebook-marko.tuxnet.local' IDENTIFIED BY 'password';

Use the following queries to monitor and evaluation table and index size.

Query below returns the size of each Database in MB.

MariaDB [(none)]> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
 FROM information_schema.tables
 GROUP BY table_schema;
+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| hope               |       75714.0 |
| information_schema |           0.2 |
| mysql              |           1.9 |
| performance_schema |           0.0 |
+--------------------+---------------+

Query below returns the size of each Table in MB.

SELECT table_schema as `Database`,table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC
LIMIT 5;       -- adjust it according to your needs
+----------+------------------------+------------+
| Database | Table                  | Size in MB |
+----------+------------------------+------------+
| hope     | eth_products           |   44029.54 |
| hope     | eth_customers          |   28868.08 |
| hope     | eth_emails             |    1423.92 |
| hope     | eth_id                 |    1392.43 |
| mysql    | help_topic             |       1.38 |
+----------+------------------------+------------+

Query below returns index size ordered from the ones using the most.

SELECT table_schema as database_name,table_name,round(index_length/1024/1024,2) as index_size
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
     and table_schema not in ('information_schema', 'sys','performance_schema', 'mysql')
     and table_schema = 'your database name'
ORDER BY index_size desc;
+---------------+------------------------+------------+
| database_name | table_name             | index_size |
+---------------+------------------------+------------+
| hope          | eth_products           |   18561.74 |
| hope          | eth_customers          |   12037.89 |
| hope          | eth_emails             |     638.70 |
| hope          | eth_id                 |     607.20 |
| hope          | eth_temp               |       0.00 |
+---------------+------------------------+------------+

The easier and the hardest way to reduce MySQL size is by deleting all the unwanted data. DB admins usually fill in DB tables or columns with unnecessary data. A DB schema reevaluation is essential to identify such cases.

The following query helps determine the last time a table is updated.

SELECT table_schema,table_name,create_time,update_time from information_schema.tables 
WHERE table_schema not in ('information_schema','mysql') 
	and engine is not null and ((update_time < (now() - interval 1 day)) or update_time is NULL) 
LIMIT 5;
+--------------+------------------------+---------------------+---------------------+
| table_schema | table_name             | create_time         | update_time         |
+--------------+------------------------+---------------------+---------------------+
| MariaDB      | eth_table1             | 2019-08-23 20:52:51 | 2019-08-23 22:54:34 |
| MariaDB      | eth_table2             | 2019-08-23 19:20:23 | 2019-08-23 19:20:23 |
| MariaDB      | eth_table3             | 2019-08-23 19:20:29 | 2019-08-23 19:20:29 |
| MariaDB      | eth_table4             | 2019-08-26 19:18:04 | 2019-08-26 21:05:10 |
| MariaDB      | eth_temp               | 2019-08-25 01:52:33 | 2019-08-25 21:16:16 |
+--------------+------------------------+---------------------+---------------------+

When all unused data are unidentified the following commands will help you delete them :

Be extra careful when using delete/drop commands ! Deleted data cannot be recovered!

DELETE FROM table1 / TRUNCATE table1        --Deletes all Records
DELETE FROM table1 WHERE condition          --Deletes records based on a condition
DROP TABLE table                            --Deletes table
DROP DATABASE                               --Deleting database
ALTER TABLE table_name DROP column_name;    --Deletes a column 

A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be and more disk space will be consumed. It is essential to track down unused indexes that consume disk space and slow down your database.

By default, statistics are not collected. This is to ensure that statistics collection does not cause any extra load on the server unless desired.

To enable statistics dynamically execute the following command :