.::: Enable userstat USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, and the TABLE_STATISTICS MariaDB MySQL :::.

A. Enable Statistic Plugin

1. Check configuration statistic
From query
show variables like '%userstat%';

From mariadb configuration
# cat /etc/my.cnf.d/server.cnf | grep userstat

 

2. Enable userstat configuration From Query ( for temporary configuration)

SET GLOBAL userstat=1;
show variables like 'userstat';

3. From mariadb configuration ( for permanent configuration) add userstat = 1 in server.cnf and then restart mariadb

[root@MCS-Dev ~]# cat /etc/my.cnf.d/server.cnf | grep userstat
userstat = 1
[root@MCS-Dev ~]# systemctl restart mariadb

4. Check status userstat configuration
show variables like 'userstat';


B. Testing User Statistic  
1. Login using root to mariadb
# Mysql -u root -p

2. Create another user (aza)

create user 'aza'@'%' identified by 'admin';
grant all privileges on *.* to 'aza'@'%' with grant option;
ALTER USER 'aza'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

create user 'aza'@'localhost' identified by 'admin';
grant all privileges on *.* to 'aza'@'localhost' with grant option;
ALTER USER 'aza'@'localhost' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

3. Login mariadb using another user (aza)
# mysql -u aza -p

C. Checking user & client statistic

1. From Mariadb Command Prompt
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS\G;
SELECT * FROM INFORMATION_SCHEMA.CLIENT_STATISTICS\G;

or

SHOW USER_STATISTICS;
SHOW CLIENT_STATISTICS;
 


2. From MySQL Workbench or dbeaver or heidisql

SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS;
SELECT * FROM INFORMATION_SCHEMA.CLIENT_STATISTICS;

or

SHOW USER_STATISTICS;
SHOW CLIENT_STATISTICS;
 

D. Checking Table statistic ( example run table pembelian )
1. Run query a table

use teguhth;
select  * from pembelian;
select  * from pasok ;

2. Check statistic table
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME= 'pembelian';
SHOW TABLE_STATISTICS;
SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE table_schema = 'teguhth';
 

E. Checking index statistic ( example run table 'barang')
1 run query
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;

2. check statistic index
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME = 'pembelian'
SHOW INDEX_STATISTICS;