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
