pgloader online mysql sql database postgresql

online - ¿Cuál es el equivalente en MySQL de EXPLICAR ANALIZAR de PostgreSQL?



pgloader (4)

Me gustaría obtener un plan de consulta detallado en MySQL similar al EXPLAIN ANALYZE que se muestra en PostgreSQL. ¿Hay un equivalente?

EXPLAIN EXTENDED

MariaDB / MySQL provee algo llamado EXPLAIN EXTENDED . Sin embargo, no hay sustituto para EXPLAIN ANALYZE . EXPLAIN EXTENDED no proporciona información de temporización alguna, y el desglose interno es mucho menos detallado.

Name: ''EXPLAIN'' Description: Syntax: EXPLAIN [explain_type] SELECT select_options explain_type: EXTENDED | PARTITIONS Or: EXPLAIN tbl_name The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE: o When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to obtain additional information. For information about using EXPLAIN and EXPLAIN EXTENDED to obtain query execution plan information, see https://mariadb.com/kb/en/explain/. o EXPLAIN PARTITIONS is useful only when examining queries involving partitioned tables. For details, see http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html. o EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. For information about DESCRIBE and SHOW COLUMNS, see [HELP DESCRIBE], and [HELP SHOW COLUMNS]. URL: https://mariadb.com/kb/en/explain/

Por ejemplo, esto se toma de este ejemplo ,

EXPLAIN ANALYZE SELECT * FROM history AS h1 WHERE EXISTS ( SELECT 1 FROM history AS h2 WHERE h1.lead_id = h2.lead_id GROUP BY lead_id HAVING count(is_first OR NULL) > 1 );

Producirá algo como esto en PostgreSQL,

QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Seq Scan on history h1 (cost=0.00..82680.50 rows=1100 width=9) (actual time=0.048..0.065 rows=3 loops=1) Filter: (SubPlan 1) Rows Removed by Filter: 3 SubPlan 1 -> GroupAggregate (cost=0.00..37.57 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=6) Group Key: h2.lead_id Filter: (count((h2.is_first OR NULL::boolean)) > 1) Rows Removed by Filter: 0 -> Seq Scan on history h2 (cost=0.00..37.50 rows=11 width=5) (actual time=0.003..0.004 rows=2 loops=6) Filter: (h1.lead_id = lead_id) Rows Removed by Filter: 4 Planning time: 0.149 ms Execution time: 0.123 ms (13 rows)

Si bien este es el equivalente de MySQL,

+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | PRIMARY | h1 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | h2 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)

solo para mayor claridad, comente sobre la respuesta aceptada (no tiene suficiente karma para agregar comentarios)

Procedure Analyse () es para un propósito diferente que EXPLICA, analiza el conjunto de datos de la columna especificada y sugiere el mejor tipo de datos, es decir, es útil cuando tenemos 1000 filas de varchar (255) y queremos verificar cuánta longitud realmente necesito, fe, podría decir que varchar (23) sería suficiente

No he usado PostgreSQL antes de que MySQL tenga EXPLAIN EXTENDED que proporciona más información que EXPLAIN y puede brindarle la información que está buscando.