Haal meer uit je MySQL database met deze handige queries

06 mei 2014

De database MySQL is één van de meest gebruikte databases op het internet. Niet iedereen is even handig met de SQL taal waarmee de database wordt aangesproken. In dit artikel laten we een aantal handige SQL statements zien om niet dagelijkse informatie naar boven te halen. Ze zijn voornamelijk gebaseerd op de database INFORMATION_SCHEMA van MySQL. Deze virtuele database bevat de metadata van de database server.

Toon alle tabellen met meer dan 1000 regels

Niet elke tabel heeft evenveel regels. De onderstaande query geeft je de tabellen over alle databases binnen de database server die meer dan 1000 regels hebben.

SELECT CONCAT(table_schema,'.',table_name) as table_name, table_rows
FROM information_schema.tables
WHERE table_rows > 1000
ORDER BY table_rows DESC;

Resultaat

+----------------------------------+------------+
| table_name                       | table_rows |
+----------------------------------+------------+
| bugs.series_data                 |      52778 |
| bugs.bugs_activity               |      26436 |
| bugs.longdescs                   |      21473 |
| bugs.email_setting               |       5370 |
| bugs.attachments                 |       4714 |
| bugs.attach_data                 |       4651 |
| bugs.cc                          |       4031 |
| bugs.bugs                        |       2190 |
| bugs.namedqueries_link_in_footer |       1228 |
+----------------------------------+------------+
9 rows in set (0.04 sec)

Toon alle tabellen die geen PRIMARY key hebben

Elke tabel hoort een PRIMARY key te hebben. Hiermee kunnen rijen in de tabellen sneller gevonden worden.

SELECT CONCAT(t.table_name,".",t.table_schema) AS table_name
FROM information_schema.TABLES AS t
LEFT JOIN information_schema.TABLE_CONSTRAINTS AS tc ON (
t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE';
)

Toon de 5 grootste tabellen

Welke tabellen over alle database binnen de database server zijn het grootst in MB's gemeten? De onderstaande query geeft je de 5 grootste, gesorteerd op grootte.

SELECT
   Concat(table_schema, '.', table_name) AS "Name"
  ,Concat(Round(table_rows / 1000000, 2), 'M') AS "Rows"
  ,Concat(Round(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') AS "Row Size"
  ,Concat(Round(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') AS "Index Size"
  ,Concat(Round(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') AS "Total"
  ,Round(index_length / data_length, 2) "Row / Index Ratio"
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 5;

Resultaat

+-------------------------------------+--------+--------+--------+------------+---------+
| concat(table_schema,'.',table_name) | rows   | data   | idx    | total_size | idxfrac |
+-------------------------------------+--------+--------+--------+------------+---------+
| art87.link_out87                    | 37.25M | 14.83G | 14.17G | 29.00G     |    0.96 |
| art87.article87                     | 12.67M | 15.83G | 4.79G  | 20.62G     |    0.30 |
| art116.article116                   | 10.49M | 12.52G | 3.65G  | 16.18G     |    0.29 |
| art84.article84                     | 10.10M | 10.11G | 3.59G  | 13.70G     |    0.35 |
| art104.link_out104                  | 23.66M | 6.63G  | 6.55G  | 13.18G     |    0.99 |
| art118.article118                   | 7.06M  | 10.49G | 2.68G  | 13.17G     |    0.26 |
| art106.article106                   | 9.86M  | 10.19G | 2.76G  | 12.95G     |    0.27 |
| art85.article85                     | 6.20M  | 9.82G  | 2.51G  | 12.33G     |    0.26 |
| art91.article91                     | 8.66M  | 9.17G  | 2.66G  | 11.83G     |    0.29 |
| art94.article94                     | 5.21M  | 10.10G | 1.69G  | 11.79G     |    0.17 |
+-------------------------------------+--------+--------+--------+------------+---------+
10 rows in set (2 min 29.19 sec)

Bronnen

Voor dit artikel zijn een aantal bronnen geraadpleegd:

Kom met ons in contact