Monthly Archives: February 2011

Analizado los campos y tipos de datos en MariaDB

Posted on February 21, 2011 by Santiago Lertora

CON SELECT PROCEDURE ANALYSE  podemos obtener la respuesta de un query con los datos optimos en base a la consulta que hicimos y los datos presentes en la tabla, aca a un ejemplo:

MariaDB [training] select * from soccer_teams procedure analyse ();
+-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
----------+
| Field_name                        | Min_value    | Max_value  | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std    | Optimal_fieldtype
|
+-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
----------+
| training.soccer_teams.id          | 1            | 2          |          1 |          1 |                0 |     0 | 1.5000                  | 0.5000 | ENUM('1','2') NOT NULL
|
| training.soccer_teams.countryid   | 1            | 2          |          1 |          1 |                0 |     0 | 1.5000                  | 0.5000 | ENUM('1','2') NOT NULL
|
| training.soccer_teams.soccer_team | Boca Juniors | Flamengo   |          8 |         12 |                0 |     0 | 10.0000                 | NULL   | ENUM('Boca Juniors','Flamengo')
NOT NULL |
| training.soccer_teams.founded     | 1895-11-17   | 1905-04-03 |         10 |         10 |                0 |     0 | 10.0000                 | NULL   | ENUM('1895-11-17','1905-04-03')
NOT NULL |
+-----------------------------------+--------------+------------+------------+------------+------------------+-------+-------------------------+--------+--------------------------------
----------+
4 rows in set (0.04 sec)
Posted in Principal | Leave a comment

Calcular el tamaño de una base de datos y tablas con Information Schema

Posted on February 21, 2011 by Santiago Lertora

Aca les dejo na breve y sencilla solucion para poder calcular los tamaños de las DB en MySQL /MariaDB y forks con Information Schema

Realmente es muy sencillo:

MariaDB [information_schema]SELECT table_name, table_rows, data_length, index_length,
-> round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
-> FROM information_schema.TABLES WHERE table_schema = "training";
+--------------+------------+-------------+--------------+------------+
| table_name   | table_rows | data_length | index_length | Size in MB |
+--------------+------------+-------------+--------------+------------+
| by_year      |          4 |       65536 |            0 |       0.06 |
| country      |          0 |           0 |         1024 |       0.00 |
| part         |          0 |       16384 |            0 |       0.02 |
| soccer_teams |          2 |       16384 |            0 |       0.02 |
| table1       |          0 |           0 |         1024 |       0.00 |
| test_aria    |          0 |        8192 |         8192 |       0.02 |
+--------------+------------+-------------+--------------+------------+
6 rows in set (0.22 sec)
Posted in Principal | Leave a comment