[MDEV-9447] view queries are not executed and block Maria DB Created: 2016-01-22  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.10, 5.5, 10.0, 10.1, 10.2.6, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Ceballos Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: optimizer, upstream-fixed
Environment:

Windows 7, Windows Server, Ubuntu 14.04

Attachments: Text File db.sql    
Sprint: 10.1.12

 Description   

I have some views, but the queries defined in those views are not executed and the server is blocked. The queries are not optimized yet, but I have tried out with mysql 5.5 and does not work, so that I decided to try out with mysql 5.6 and 5.7 and the queries work normally.

The views defined are:

  • view_rip_medicamento
  • vista_rip_consulta

I suppose the another queries have the same problem.

I leave the script with all necessary tables and views

 Comments   
Comment by Elena Stepanova [ 2016-01-22 ]

Thanks for the report and test case.

Views don't matter, the underlying query matters.
The query gets executed, it just takes extremely long. Most of time is spent in 'statistics' state, probably that's why EXPLAIN takes equally long.
Sometimes ANALYZE helps a bit, at least it allowed me to wait till the query and EXPLAIN finishes. On my machine, it took 2+ minutes. Other times it's much longer, I had to interrupt it.
With MySQL 5.6, the query and EXPLAIN are nearly instant.

Query

 
select `adm_ingresos`.`id_usuario` AS `id_usuario`,`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`adm_usuarios`.`nombre1` AS `nombre1`,`adm_usuarios`.`nombre2` AS `nombre2`,`adm_usuarios`.`apellido1` AS `apellido1`,`adm_usuarios`.`apellido2` AS `apellido2`,`adm_usuarios`.`sexo` AS `sexo`,`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`adm_ingresos`.`direccion` AS `direccion`,`adm_ingresos`.`telefono` AS `telefono`,`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`tb_municipios`.`nom_municipio` AS `nom_municipio`,`adm_usuarios`.`zona` AS `zona`,`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`tb_eps`.`codigo_eps` AS `codigo_eps`,`tb_eps`.`razon_social` AS `nombre_eps`,`tb_regimenes`.`descripcion_reg` AS `regimen`,`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`tb_medico`.`nombre1`,' ',`tb_medico`.`nombre2`,' ',`tb_medico`.`apellido1`,' ',`tb_medico`.`apellido2`) AS `nom_medico`,`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`adm_ingresos`.`id_ambito` AS `id_ambito`,`tb_ambitos`.`descripcion_ambito` AS `ambito`,`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`adm_ingresos`.`num_orden` AS `num_orden`,`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`tb_causa_externa`.`descripcion` AS `causa_externa`,`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`far_medicamento_lote`.`cum` AS `cum`,`far_medicamentos`.`atc` AS `atc`,`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`far_medicamentos`.`concentracion` AS `concentracion`,`far_med_unidad`.`descripcion` AS `unidad_medida`,`far_for_farmaceutica`.`descripcion` AS `presentacion`,`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`fac_facturacion`.`num_factura` AS `num_factura`,`fac_facturacion`.`estado` AS `estado`,`fac_facturacion`.`fec_factura` AS `fec_factura`,`fac_facturacion`.`hor_factura` AS `hor_factura`,`tb_contratos`.`num_contrato` AS `num_contrato`,`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`fac_facturacion_detalle`.`valor` AS `valor`,(`fac_facturacion_detalle`.`cantidad` * `fac_facturacion_detalle`.`valor`) AS `valor_total` from (((((((((((((((((((((((`adm_ingresos_detalle` join `adm_ingresos` on((`adm_ingresos`.`id_ingreso` = `adm_ingresos_detalle`.`id_ingreso`))) join `adm_estadoingreso` on((`adm_estadoingreso`.`id_est_servicio` = `adm_ingresos`.`id_est_servicio`))) join `tb_ocupaciones` on((`tb_ocupaciones`.`id_ocupacion` = `adm_ingresos`.`id_ocupacion`))) join `tb_ambitos` on((`tb_ambitos`.`id_ambito` = `adm_ingresos`.`id_ambito`))) join `tb_eps` on((`tb_eps`.`id_eps` = `adm_ingresos`.`id_eps`))) join `tb_regimenes` on((`tb_regimenes`.`id_regimen` = `adm_ingresos`.`id_regimen`))) join `tb_causa_externa` on((`tb_causa_externa`.`id_causa_externa` = `adm_ingresos`.`id_cau_externa`))) join `tb_cie10` `tb_dx_ing` on((`tb_dx_ing`.`id_cie` = `adm_ingresos`.`id_dx_ing_principal`))) join `adm_usuarios` on((`adm_usuarios`.`id_usr_salud` = `adm_ingresos`.`id_usuario`))) join `tb_tipo_documento` on((`tb_tipo_documento`.`id_tipo_doc` = `adm_usuarios`.`id_tipo_doc_usr`))) join `tb_barriosveredas` on((`tb_barriosveredas`.`id_barriovereda` = `adm_usuarios`.`id_barriovereda`))) join `tb_municipios` on((`tb_municipios`.`id_municipio` = `adm_usuarios`.`id_municipio`))) join `tb_tipo_poblacion` on((`tb_tipo_poblacion`.`id_tipo_pob` = `adm_usuarios`.`id_tipo_poblacion`))) join `tb_etnias` on((`tb_etnias`.`id_etnia` = `adm_usuarios`.`id_etnia`))) join `seg_usuarios_sistema` `tb_medico` on((`tb_medico`.`id_usuario` = `adm_ingresos_detalle`.`id_profesional`))) join `far_medicamento_lote` on((`far_medicamento_lote`.`id_cum` = `adm_ingresos_detalle`.`id_medicamento`))) join `far_medicamentos` on((`far_medicamentos`.`id_med` = `far_medicamento_lote`.`id_med`))) join `tb_serv_tipo` on((`tb_serv_tipo`.`id_tipo_servicio` = `far_medicamentos`.`id_tip_medicamento`))) join `far_med_unidad` on((`far_med_unidad`.`id_uni` = `far_medicamentos`.`id_unidadmedida`))) join `far_for_farmaceutica` on((`far_for_farmaceutica`.`id_for` = `far_medicamentos`.`id_formafarmaceutica`))) left join `fac_facturacion_detalle` on((`fac_facturacion_detalle`.`id_ing_detalle` = `adm_ingresos_detalle`.`id_ing_detalle`))) left join `fac_facturacion` on((`fac_facturacion`.`id_factura` = `fac_facturacion_detalle`.`id_factura`))) left join `tb_contratos` on((`tb_contratos`.`id_contrato` = `fac_facturacion`.`id_contrato`))) where (`tb_serv_tipo`.`plano` = 'AM');

10.1 EXPLAIN

 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
| id   | select_type | table                   | type   | possible_keys                                                                                                                                             | key     | key_len | ref                                        | rows | filtered | Extra                                           |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | adm_ingresos_detalle    | ALL    | FK_adm_ingresos_detalle1,FK_adm_ingresos_detalle,FK_adm_ingresos_detalle12                                                                                | NULL    | NULL    | NULL                                       |   12 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | fac_facturacion_detalle | ALL    | FK_fac_facturacion_detalle1                                                                                                                               | NULL    | NULL    | NULL                                       |    1 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | fac_facturacion         | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion_detalle.id_factura    |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_contratos            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion.id_contrato           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_medico               | eq_ref | PRIMARY,id                                                                                                                                                | PRIMARY | 4       | test.adm_ingresos_detalle.id_profesional   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_ingresos            | eq_ref | PRIMARY,FK_adm_ingresos3,FK_adm_ingresos4,FK_adm_ingresos6,FK_adm_ingresos11,FK_adm_ingresos5,FK_adm_ingresos18,FK_adm_ingresos25,FK_adm_ingresos26       | PRIMARY | 4       | test.adm_ingresos_detalle.id_ingreso       |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | adm_estadoingreso       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 1       | test.adm_ingresos.id_est_servicio          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_causa_externa        | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_cau_externa           |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ocupaciones          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_ocupacion             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_medicamento_lote    | eq_ref | PRIMARY,FK_far_medicamento_lote                                                                                                                           | PRIMARY | 4       | test.adm_ingresos_detalle.id_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_medicamentos        | eq_ref | PRIMARY,FK_tb_medicamentos2,FK_tb_medicamentos,FK_tb_medicamentos1                                                                                        | PRIMARY | 4       | test.far_medicamento_lote.id_med           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_serv_tipo            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_tip_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_for_farmaceutica    | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_formafarmaceutica |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_med_unidad          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_unidadmedida      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_usuarios            | eq_ref | PRIMARY,tipo_documento,num_documento,tipo_id,tipo_usuario,FK_tb_identificacion_usuarios21,FK_tb_identificacion_usuarios16,FK_tb_identificacion_usuarios15 | PRIMARY | 4       | test.adm_ingresos.id_usuario               |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_tipo_poblacion       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_poblacion        |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_barriosveredas       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_barriovereda          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_tipo_documento       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_doc_usr          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_municipios           | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_municipio             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_dx_ing               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_dx_ing_principal      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ambitos              | ALL    | PRIMARY                                                                                                                                                   | NULL    | NULL    | NULL                                       |    4 |    75.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | tb_eps                  | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_eps                   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_etnias               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_etnia                 |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_regimenes            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_regimen               |    1 |   100.00 |                                                 |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`adm_ingresos`.`id_usuario` AS `id_usuario`,`test`.`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`test`.`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`test`.`adm_usuarios`.`nombre1` AS `nombre1`,`test`.`adm_usuarios`.`nombre2` AS `nombre2`,`test`.`adm_usuarios`.`apellido1` AS `apellido1`,`test`.`adm_usuarios`.`apellido2` AS `apellido2`,`test`.`adm_usuarios`.`sexo` AS `sexo`,`test`.`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`test`.`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`test`.`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`test`.`adm_ingresos`.`direccion` AS `direccion`,`test`.`adm_ingresos`.`telefono` AS `telefono`,`test`.`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`test`.`tb_municipios`.`nom_municipio` AS `nom_municipio`,`test`.`adm_usuarios`.`zona` AS `zona`,`test`.`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`test`.`tb_eps`.`codigo_eps` AS `codigo_eps`,`test`.`tb_eps`.`razon_social` AS `nombre_eps`,`test`.`tb_regimenes`.`descripcion_reg` AS `regimen`,`test`.`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`test`.`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`test`.`tb_medico`.`nombre1`,' ',`test`.`tb_medico`.`nombre2`,' ',`test`.`tb_medico`.`apellido1`,' ',`test`.`tb_medico`.`apellido2`) AS `nom_medico`,`test`.`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`test`.`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`test`.`adm_ingresos`.`id_ambito` AS `id_ambito`,`test`.`tb_ambitos`.`descripcion_ambito` AS `ambito`,`test`.`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`test`.`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`test`.`adm_ingresos`.`num_orden` AS `num_orden`,`test`.`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`test`.`tb_causa_externa`.`descripcion` AS `causa_externa`,`test`.`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`test`.`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`test`.`far_medicamento_lote`.`cum` AS `cum`,`test`.`far_medicamentos`.`atc` AS `atc`,`test`.`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `test`.`tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`test`.`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`test`.`far_medicamentos`.`concentracion` AS `concentracion`,`test`.`far_med_unidad`.`descripcion` AS `unidad_medida`,`test`.`far_for_farmaceutica`.`descripcion` AS `presentacion`,`test`.`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`test`.`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`test`.`fac_facturacion`.`num_factura` AS `num_factura`,`test`.`fac_facturacion`.`estado` AS `estado`,`test`.`fac_facturacion`.`fec_factura` AS `fec_factura`,`test`.`fac_facturacion`.`hor_factura` AS `hor_factura`,`test`.`tb_contratos`.`num_contrato` AS `num_contrato`,`test`.`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`test`.`fac_facturacion_detalle`.`valor` AS `valor`,(`test`.`fac_facturacion_detalle`.`cantidad` * `test`.`fac_facturacion_detalle`.`valor`) AS `valor_total` from `test`.`adm_ingresos_detalle` join `test`.`adm_ingresos` join `test`.`adm_estadoingreso` join `test`.`tb_ocupaciones` join `test`.`tb_ambitos` join `test`.`tb_eps` join `test`.`tb_regimenes` join `test`.`tb_causa_externa` join `test`.`tb_cie10` `tb_dx_ing` join `test`.`adm_usuarios` join `test`.`tb_tipo_documento` join `test`.`tb_barriosveredas` join `test`.`tb_municipios` join `test`.`tb_tipo_poblacion` join `test`.`tb_etnias` join `test`.`seg_usuarios_sistema` `tb_medico` join `test`.`far_medicamento_lote` join `test`.`far_medicamentos` join `test`.`tb_serv_tipo` join `test`.`far_med_unidad` join `test`.`far_for_farmaceutica` left join `test`.`fac_facturacion_detalle` on((`test`.`fac_facturacion_detalle`.`id_ing_detalle` = `test`.`adm_ingresos_detalle`.`id_ing_detalle`)) left join `test`.`fac_facturacion` on(((`test`.`fac_facturacion`.`id_factura` = `test`.`fac_facturacion_detalle`.`id_factura`) and (`test`.`fac_facturacion_detalle`.`id_factura` is not null))) left join `test`.`tb_contratos` on(((`test`.`tb_contratos`.`id_contrato` = `test`.`fac_facturacion`.`id_contrato`) and (`test`.`fac_facturacion`.`id_contrato` is not null))) where ((`test`.`far_for_farmaceutica`.`id_for` = `test`.`far_medicamentos`.`id_formafarmaceutica`) and (`test`.`far_med_unidad`.`id_uni` = `test`.`far_medicamentos`.`id_unidadmedida`) and (`test`.`tb_serv_tipo`.`id_tipo_servicio` = `test`.`far_medicamentos`.`id_tip_medicamento`) and (`test`.`far_medicamentos`.`id_med` = `test`.`far_medicamento_lote`.`id_med`) and (`test`.`far_medicamento_lote`.`id_cum` = `test`.`adm_ingresos_detalle`.`id_medicamento`) and (`test`.`tb_medico`.`id_usuario` = `test`.`adm_ingresos_detalle`.`id_profesional`) and (`test`.`tb_etnias`.`id_etnia` = `test`.`adm_usuarios`.`id_etnia`) and (`test`.`tb_tipo_poblacion`.`id_tipo_pob` = `test`.`adm_usuarios`.`id_tipo_poblacion`) and (`test`.`tb_municipios`.`id_municipio` = `test`.`adm_usuarios`.`id_municipio`) and (`test`.`tb_barriosveredas`.`id_barriovereda` = `test`.`adm_usuarios`.`id_barriovereda`) and (`test`.`tb_tipo_documento`.`id_tipo_doc` = `test`.`adm_usuarios`.`id_tipo_doc_usr`) and (`test`.`adm_usuarios`.`id_usr_salud` = `test`.`adm_ingresos`.`id_usuario`) and (`test`.`tb_dx_ing`.`id_cie` = `test`.`adm_ingresos`.`id_dx_ing_principal`) and (`test`.`tb_causa_externa`.`id_causa_externa` = `test`.`adm_ingresos`.`id_cau_externa`) and (`test`.`tb_regimenes`.`id_regimen` = `test`.`adm_ingresos`.`id_regimen`) and (`test`.`tb_eps`.`id_eps` = `test`.`adm_ingresos`.`id_eps`) and (`test`.`tb_ambitos`.`id_ambito` = `test`.`adm_ingresos`.`id_ambito`) and (`test`.`tb_ocupaciones`.`id_ocupacion` = `test`.`adm_ingresos`.`id_ocupacion`) and (`test`.`adm_estadoingreso`.`id_est_servicio` = `test`.`adm_ingresos`.`id_est_servicio`) and (`test`.`adm_ingresos`.`id_ingreso` = `test`.`adm_ingresos_detalle`.`id_ingreso`) and (`test`.`tb_serv_tipo`.`plano` = 'AM')) |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

10.1 Status

 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 0     |
 
| Handler_icp_attempts       | 0     |
 
| Handler_icp_match          | 0     |
 
| Handler_mrr_init           | 0     |
 
| Handler_mrr_key_refills    | 0     |
 
| Handler_mrr_rowid_refills  | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 0     |
 
| Handler_read_key           | 19    |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 0     |
 
| Handler_read_prev          | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_deleted   | 0     |
 
| Handler_read_rnd_next      | 19    |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_tmp_update         | 0     |
 
| Handler_tmp_write          | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |

MySQL 5.6 EXPLAIN

 
+----+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------------+
 
| id | select_type | table                   | type   | possible_keys                                                                                                                                             | key                     | key_len | ref                                        | rows | filtered | Extra                                              |
 
+----+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------------+
 
|  1 | SIMPLE      | tb_ambitos              | ALL    | PRIMARY                                                                                                                                                   | NULL                    | NULL    | NULL                                       |    4 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | adm_ingresos            | ref    | PRIMARY,FK_adm_ingresos3,FK_adm_ingresos4,FK_adm_ingresos6,FK_adm_ingresos11,FK_adm_ingresos5,FK_adm_ingresos18,FK_adm_ingresos25,FK_adm_ingresos26       | FK_adm_ingresos3        | 2       | test.tb_ambitos.id_ambito                  |    2 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | adm_estadoingreso       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 1       | test.adm_ingresos.id_est_servicio          |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | adm_ingresos_detalle    | ref    | FK_adm_ingresos_detalle1,FK_adm_ingresos_detalle,FK_adm_ingresos_detalle12                                                                                | FK_adm_ingresos_detalle | 5       | test.adm_ingresos.id_ingreso               |    1 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | tb_medico               | eq_ref | PRIMARY,id                                                                                                                                                | PRIMARY                 | 4       | test.adm_ingresos_detalle.id_profesional   |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_causa_externa        | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_ingresos.id_cau_externa           |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_ocupaciones          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_ingresos.id_ocupacion             |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | far_medicamento_lote    | eq_ref | PRIMARY,FK_far_medicamento_lote                                                                                                                           | PRIMARY                 | 4       | test.adm_ingresos_detalle.id_medicamento   |    1 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | far_medicamentos        | eq_ref | PRIMARY,FK_tb_medicamentos2,FK_tb_medicamentos,FK_tb_medicamentos1                                                                                        | PRIMARY                 | 4       | test.far_medicamento_lote.id_med           |    1 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | tb_serv_tipo            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.far_medicamentos.id_tip_medicamento   |    1 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | far_for_farmaceutica    | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.far_medicamentos.id_formafarmaceutica |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | far_med_unidad          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.far_medicamentos.id_unidadmedida      |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | adm_usuarios            | eq_ref | PRIMARY,tipo_documento,num_documento,tipo_id,tipo_usuario,FK_tb_identificacion_usuarios21,FK_tb_identificacion_usuarios16,FK_tb_identificacion_usuarios15 | PRIMARY                 | 4       | test.adm_ingresos.id_usuario               |    1 |   100.00 | Using where                                        |
 
|  1 | SIMPLE      | tb_tipo_documento       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_usuarios.id_tipo_doc_usr          |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_barriosveredas       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_usuarios.id_barriovereda          |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_tipo_poblacion       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_usuarios.id_tipo_poblacion        |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_municipios           | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_usuarios.id_municipio             |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_dx_ing               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_ingresos.id_dx_ing_principal      |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | fac_facturacion_detalle | ALL    | FK_fac_facturacion_detalle1                                                                                                                               | NULL                    | NULL    | NULL                                       |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
 
|  1 | SIMPLE      | fac_facturacion         | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.fac_facturacion_detalle.id_factura    |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_contratos            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.fac_facturacion.id_contrato           |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_eps                  | ALL    | PRIMARY                                                                                                                                                   | NULL                    | NULL    | NULL                                       |    5 |    80.00 | Using where; Using join buffer (Block Nested Loop) |
 
|  1 | SIMPLE      | tb_regimenes            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_ingresos.id_regimen               |    1 |   100.00 | NULL                                               |
 
|  1 | SIMPLE      | tb_etnias               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                 | 4       | test.adm_usuarios.id_etnia                 |    1 |   100.00 | NULL                                               |
 
+----+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------------+
 

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | /* select#1 */ select `test`.`adm_ingresos`.`id_usuario` AS `id_usuario`,`test`.`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`test`.`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`test`.`adm_usuarios`.`nombre1` AS `nombre1`,`test`.`adm_usuarios`.`nombre2` AS `nombre2`,`test`.`adm_usuarios`.`apellido1` AS `apellido1`,`test`.`adm_usuarios`.`apellido2` AS `apellido2`,`test`.`adm_usuarios`.`sexo` AS `sexo`,`test`.`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`test`.`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`test`.`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`test`.`adm_ingresos`.`direccion` AS `direccion`,`test`.`adm_ingresos`.`telefono` AS `telefono`,`test`.`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`test`.`tb_municipios`.`nom_municipio` AS `nom_municipio`,`test`.`adm_usuarios`.`zona` AS `zona`,`test`.`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`test`.`tb_eps`.`codigo_eps` AS `codigo_eps`,`test`.`tb_eps`.`razon_social` AS `nombre_eps`,`test`.`tb_regimenes`.`descripcion_reg` AS `regimen`,`test`.`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`test`.`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`test`.`tb_medico`.`nombre1`,' ',`test`.`tb_medico`.`nombre2`,' ',`test`.`tb_medico`.`apellido1`,' ',`test`.`tb_medico`.`apellido2`) AS `nom_medico`,`test`.`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`test`.`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`test`.`adm_ingresos`.`id_ambito` AS `id_ambito`,`test`.`tb_ambitos`.`descripcion_ambito` AS `ambito`,`test`.`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`test`.`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`test`.`adm_ingresos`.`num_orden` AS `num_orden`,`test`.`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`test`.`tb_causa_externa`.`descripcion` AS `causa_externa`,`test`.`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`test`.`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`test`.`far_medicamento_lote`.`cum` AS `cum`,`test`.`far_medicamentos`.`atc` AS `atc`,`test`.`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `test`.`tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`test`.`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`test`.`far_medicamentos`.`concentracion` AS `concentracion`,`test`.`far_med_unidad`.`descripcion` AS `unidad_medida`,`test`.`far_for_farmaceutica`.`descripcion` AS `presentacion`,`test`.`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`test`.`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`test`.`fac_facturacion`.`num_factura` AS `num_factura`,`test`.`fac_facturacion`.`estado` AS `estado`,`test`.`fac_facturacion`.`fec_factura` AS `fec_factura`,`test`.`fac_facturacion`.`hor_factura` AS `hor_factura`,`test`.`tb_contratos`.`num_contrato` AS `num_contrato`,`test`.`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`test`.`fac_facturacion_detalle`.`valor` AS `valor`,(`test`.`fac_facturacion_detalle`.`cantidad` * `test`.`fac_facturacion_detalle`.`valor`) AS `valor_total` from `test`.`adm_ingresos_detalle` join `test`.`adm_ingresos` join `test`.`adm_estadoingreso` join `test`.`tb_ocupaciones` join `test`.`tb_ambitos` join `test`.`tb_eps` join `test`.`tb_regimenes` join `test`.`tb_causa_externa` join `test`.`tb_cie10` `tb_dx_ing` join `test`.`adm_usuarios` join `test`.`tb_tipo_documento` join `test`.`tb_barriosveredas` join `test`.`tb_municipios` join `test`.`tb_tipo_poblacion` join `test`.`tb_etnias` join `test`.`seg_usuarios_sistema` `tb_medico` join `test`.`far_medicamento_lote` join `test`.`far_medicamentos` join `test`.`tb_serv_tipo` join `test`.`far_med_unidad` join `test`.`far_for_farmaceutica` left join `test`.`fac_facturacion_detalle` on((`test`.`fac_facturacion_detalle`.`id_ing_detalle` = `test`.`adm_ingresos_detalle`.`id_ing_detalle`)) left join `test`.`fac_facturacion` on((`test`.`fac_facturacion`.`id_factura` = `test`.`fac_facturacion_detalle`.`id_factura`)) left join `test`.`tb_contratos` on((`test`.`tb_contratos`.`id_contrato` = `test`.`fac_facturacion`.`id_contrato`)) where ((`test`.`adm_ingresos_detalle`.`id_ingreso` = `test`.`adm_ingresos`.`id_ingreso`) and (`test`.`adm_estadoingreso`.`id_est_servicio` = `test`.`adm_ingresos`.`id_est_servicio`) and (`test`.`tb_ocupaciones`.`id_ocupacion` = `test`.`adm_ingresos`.`id_ocupacion`) and (`test`.`adm_ingresos`.`id_ambito` = `test`.`tb_ambitos`.`id_ambito`) and (`test`.`tb_eps`.`id_eps` = `test`.`adm_ingresos`.`id_eps`) and (`test`.`tb_regimenes`.`id_regimen` = `test`.`adm_ingresos`.`id_regimen`) and (`test`.`tb_causa_externa`.`id_causa_externa` = `test`.`adm_ingresos`.`id_cau_externa`) and (`test`.`tb_dx_ing`.`id_cie` = `test`.`adm_ingresos`.`id_dx_ing_principal`) and (`test`.`adm_usuarios`.`id_usr_salud` = `test`.`adm_ingresos`.`id_usuario`) and (`test`.`tb_tipo_documento`.`id_tipo_doc` = `test`.`adm_usuarios`.`id_tipo_doc_usr`) and (`test`.`tb_barriosveredas`.`id_barriovereda` = `test`.`adm_usuarios`.`id_barriovereda`) and (`test`.`tb_municipios`.`id_municipio` = `test`.`adm_usuarios`.`id_municipio`) and (`test`.`tb_tipo_poblacion`.`id_tipo_pob` = `test`.`adm_usuarios`.`id_tipo_poblacion`) and (`test`.`tb_etnias`.`id_etnia` = `test`.`adm_usuarios`.`id_etnia`) and (`test`.`tb_medico`.`id_usuario` = `test`.`adm_ingresos_detalle`.`id_profesional`) and (`test`.`far_medicamento_lote`.`id_cum` = `test`.`adm_ingresos_detalle`.`id_medicamento`) and (`test`.`far_medicamentos`.`id_med` = `test`.`far_medicamento_lote`.`id_med`) and (`test`.`tb_serv_tipo`.`id_tipo_servicio` = `test`.`far_medicamentos`.`id_tip_medicamento`) and (`test`.`far_med_unidad`.`id_uni` = `test`.`far_medicamentos`.`id_unidadmedida`) and (`test`.`far_for_farmaceutica`.`id_for` = `test`.`far_medicamentos`.`id_formafarmaceutica`) and (`test`.`tb_serv_tipo`.`plano` = 'AM')) |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQL 5.6 status

 
| Handler_commit             | 1     |
 
| Handler_delete             | 0     |
 
| Handler_discover           | 0     |
 
| Handler_external_lock      | 48    |
 
| Handler_mrr_init           | 0     |
 
| Handler_prepare            | 0     |
 
| Handler_read_first         | 3     |
 
| Handler_read_key           | 37    |
 
| Handler_read_last          | 0     |
 
| Handler_read_next          | 25    |
 
| Handler_read_prev          | 0     |
 
| Handler_read_rnd           | 0     |
 
| Handler_read_rnd_next      | 12    |
 
| Handler_rollback           | 0     |
 
| Handler_savepoint          | 0     |
 
| Handler_savepoint_rollback | 0     |
 
| Handler_update             | 0     |
 
| Handler_write              | 0     |

Comment by Sergei Petrunia [ 2016-01-22 ]

elenst, thanks for all the details.

Looking at the queries. They are 24-way joins.

  • There are 3 LEFT JOINs. They are not converted to inner joins by either MySQL or MariaDB.
  • contents of the WHERE clause after optimization is the same in MySQL and MariaDB (save the order of clauses fields. These are different due to equality substitution).
  • ON expressions are different (MariaDB has two injected IS NOT NULL predicates while MySQL doesn't (or are they just not shown?))

elenst, is it possible to check

  • what happens at lower @@optimizer_search_depth values? (e.g. 1 and 5) ?
  • If the above doesn't show difference, check whether setting @@join_cache_level=0 has any effect.
Comment by Elena Stepanova [ 2016-01-23 ]

optimizer_search_depth does make a difference.
optimizer_search_depth=5 is good – it seems to be able to find the same plan as the default 62.
optimizer_search_depth=1 is somewhat worse than 5, but still much much better than the default 62.

Now I ran it on a release build to get more sensible execution time. ANALYZE TABLE was still executed.

defaults
37 seconds

+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
| id   | select_type | table                   | type   | possible_keys                                                                                                                                             | key     | key_len | ref                                        | rows | filtered | Extra                                           |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | adm_ingresos_detalle    | ALL    | FK_adm_ingresos_detalle1,FK_adm_ingresos_detalle,FK_adm_ingresos_detalle12                                                                                | NULL    | NULL    | NULL                                       |   12 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | fac_facturacion_detalle | ALL    | FK_fac_facturacion_detalle1                                                                                                                               | NULL    | NULL    | NULL                                       |    1 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | fac_facturacion         | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion_detalle.id_factura    |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_contratos            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion.id_contrato           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_medico               | eq_ref | PRIMARY,id                                                                                                                                                | PRIMARY | 4       | test.adm_ingresos_detalle.id_profesional   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_ingresos            | eq_ref | PRIMARY,FK_adm_ingresos3,FK_adm_ingresos4,FK_adm_ingresos6,FK_adm_ingresos11,FK_adm_ingresos5,FK_adm_ingresos18,FK_adm_ingresos25,FK_adm_ingresos26       | PRIMARY | 4       | test.adm_ingresos_detalle.id_ingreso       |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | adm_estadoingreso       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 1       | test.adm_ingresos.id_est_servicio          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_causa_externa        | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_cau_externa           |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ocupaciones          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_ocupacion             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_medicamento_lote    | eq_ref | PRIMARY,FK_far_medicamento_lote                                                                                                                           | PRIMARY | 4       | test.adm_ingresos_detalle.id_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_medicamentos        | eq_ref | PRIMARY,FK_tb_medicamentos2,FK_tb_medicamentos,FK_tb_medicamentos1                                                                                        | PRIMARY | 4       | test.far_medicamento_lote.id_med           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_serv_tipo            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_tip_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_for_farmaceutica    | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_formafarmaceutica |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_med_unidad          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_unidadmedida      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_usuarios            | eq_ref | PRIMARY,tipo_documento,num_documento,tipo_id,tipo_usuario,FK_tb_identificacion_usuarios21,FK_tb_identificacion_usuarios16,FK_tb_identificacion_usuarios15 | PRIMARY | 4       | test.adm_ingresos.id_usuario               |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_tipo_poblacion       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_poblacion        |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_barriosveredas       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_barriovereda          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_tipo_documento       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_doc_usr          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_municipios           | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_municipio             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_dx_ing               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_dx_ing_principal      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ambitos              | ALL    | PRIMARY                                                                                                                                                   | NULL    | NULL    | NULL                                       |    4 |    75.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | tb_eps                  | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_eps                   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_etnias               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_etnia                 |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_regimenes            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_regimen               |    1 |   100.00 |                                                 |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`adm_ingresos`.`id_usuario` AS `id_usuario`,`test`.`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`test`.`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`test`.`adm_usuarios`.`nombre1` AS `nombre1`,`test`.`adm_usuarios`.`nombre2` AS `nombre2`,`test`.`adm_usuarios`.`apellido1` AS `apellido1`,`test`.`adm_usuarios`.`apellido2` AS `apellido2`,`test`.`adm_usuarios`.`sexo` AS `sexo`,`test`.`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`test`.`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`test`.`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`test`.`adm_ingresos`.`direccion` AS `direccion`,`test`.`adm_ingresos`.`telefono` AS `telefono`,`test`.`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`test`.`tb_municipios`.`nom_municipio` AS `nom_municipio`,`test`.`adm_usuarios`.`zona` AS `zona`,`test`.`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`test`.`tb_eps`.`codigo_eps` AS `codigo_eps`,`test`.`tb_eps`.`razon_social` AS `nombre_eps`,`test`.`tb_regimenes`.`descripcion_reg` AS `regimen`,`test`.`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`test`.`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`test`.`tb_medico`.`nombre1`,' ',`test`.`tb_medico`.`nombre2`,' ',`test`.`tb_medico`.`apellido1`,' ',`test`.`tb_medico`.`apellido2`) AS `nom_medico`,`test`.`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`test`.`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`test`.`adm_ingresos`.`id_ambito` AS `id_ambito`,`test`.`tb_ambitos`.`descripcion_ambito` AS `ambito`,`test`.`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`test`.`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`test`.`adm_ingresos`.`num_orden` AS `num_orden`,`test`.`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`test`.`tb_causa_externa`.`descripcion` AS `causa_externa`,`test`.`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`test`.`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`test`.`far_medicamento_lote`.`cum` AS `cum`,`test`.`far_medicamentos`.`atc` AS `atc`,`test`.`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `test`.`tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`test`.`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`test`.`far_medicamentos`.`concentracion` AS `concentracion`,`test`.`far_med_unidad`.`descripcion` AS `unidad_medida`,`test`.`far_for_farmaceutica`.`descripcion` AS `presentacion`,`test`.`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`test`.`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`test`.`fac_facturacion`.`num_factura` AS `num_factura`,`test`.`fac_facturacion`.`estado` AS `estado`,`test`.`fac_facturacion`.`fec_factura` AS `fec_factura`,`test`.`fac_facturacion`.`hor_factura` AS `hor_factura`,`test`.`tb_contratos`.`num_contrato` AS `num_contrato`,`test`.`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`test`.`fac_facturacion_detalle`.`valor` AS `valor`,(`test`.`fac_facturacion_detalle`.`cantidad` * `test`.`fac_facturacion_detalle`.`valor`) AS `valor_total` from `test`.`adm_ingresos_detalle` join `test`.`adm_ingresos` join `test`.`adm_estadoingreso` join `test`.`tb_ocupaciones` join `test`.`tb_ambitos` join `test`.`tb_eps` join `test`.`tb_regimenes` join `test`.`tb_causa_externa` join `test`.`tb_cie10` `tb_dx_ing` join `test`.`adm_usuarios` join `test`.`tb_tipo_documento` join `test`.`tb_barriosveredas` join `test`.`tb_municipios` join `test`.`tb_tipo_poblacion` join `test`.`tb_etnias` join `test`.`seg_usuarios_sistema` `tb_medico` join `test`.`far_medicamento_lote` join `test`.`far_medicamentos` join `test`.`tb_serv_tipo` join `test`.`far_med_unidad` join `test`.`far_for_farmaceutica` left join `test`.`fac_facturacion_detalle` on((`test`.`fac_facturacion_detalle`.`id_ing_detalle` = `test`.`adm_ingresos_detalle`.`id_ing_detalle`)) left join `test`.`fac_facturacion` on(((`test`.`fac_facturacion`.`id_factura` = `test`.`fac_facturacion_detalle`.`id_factura`) and (`test`.`fac_facturacion_detalle`.`id_factura` is not null))) left join `test`.`tb_contratos` on(((`test`.`tb_contratos`.`id_contrato` = `test`.`fac_facturacion`.`id_contrato`) and (`test`.`fac_facturacion`.`id_contrato` is not null))) where ((`test`.`far_for_farmaceutica`.`id_for` = `test`.`far_medicamentos`.`id_formafarmaceutica`) and (`test`.`far_med_unidad`.`id_uni` = `test`.`far_medicamentos`.`id_unidadmedida`) and (`test`.`tb_serv_tipo`.`id_tipo_servicio` = `test`.`far_medicamentos`.`id_tip_medicamento`) and (`test`.`far_medicamentos`.`id_med` = `test`.`far_medicamento_lote`.`id_med`) and (`test`.`far_medicamento_lote`.`id_cum` = `test`.`adm_ingresos_detalle`.`id_medicamento`) and (`test`.`tb_medico`.`id_usuario` = `test`.`adm_ingresos_detalle`.`id_profesional`) and (`test`.`tb_etnias`.`id_etnia` = `test`.`adm_usuarios`.`id_etnia`) and (`test`.`tb_tipo_poblacion`.`id_tipo_pob` = `test`.`adm_usuarios`.`id_tipo_poblacion`) and (`test`.`tb_municipios`.`id_municipio` = `test`.`adm_usuarios`.`id_municipio`) and (`test`.`tb_barriosveredas`.`id_barriovereda` = `test`.`adm_usuarios`.`id_barriovereda`) and (`test`.`tb_tipo_documento`.`id_tipo_doc` = `test`.`adm_usuarios`.`id_tipo_doc_usr`) and (`test`.`adm_usuarios`.`id_usr_salud` = `test`.`adm_ingresos`.`id_usuario`) and (`test`.`tb_dx_ing`.`id_cie` = `test`.`adm_ingresos`.`id_dx_ing_principal`) and (`test`.`tb_causa_externa`.`id_causa_externa` = `test`.`adm_ingresos`.`id_cau_externa`) and (`test`.`tb_regimenes`.`id_regimen` = `test`.`adm_ingresos`.`id_regimen`) and (`test`.`tb_eps`.`id_eps` = `test`.`adm_ingresos`.`id_eps`) and (`test`.`tb_ambitos`.`id_ambito` = `test`.`adm_ingresos`.`id_ambito`) and (`test`.`tb_ocupaciones`.`id_ocupacion` = `test`.`adm_ingresos`.`id_ocupacion`) and (`test`.`adm_estadoingreso`.`id_est_servicio` = `test`.`adm_ingresos`.`id_est_servicio`) and (`test`.`adm_ingresos`.`id_ingreso` = `test`.`adm_ingresos_detalle`.`id_ingreso`) and (`test`.`tb_serv_tipo`.`plano` = 'AM')) |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

to be continued (does not fit into one comment)

Comment by Elena Stepanova [ 2016-01-23 ]

optimizer_search_depth=5
0.01 sec

+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
| id   | select_type | table                   | type   | possible_keys                                                                                                                                             | key     | key_len | ref                                        | rows | filtered | Extra                                           |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 
|    1 | SIMPLE      | adm_ingresos_detalle    | ALL    | FK_adm_ingresos_detalle1,FK_adm_ingresos_detalle,FK_adm_ingresos_detalle12                                                                                | NULL    | NULL    | NULL                                       |   12 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | fac_facturacion_detalle | ALL    | FK_fac_facturacion_detalle1                                                                                                                               | NULL    | NULL    | NULL                                       |    1 |   100.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | fac_facturacion         | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion_detalle.id_factura    |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_contratos            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.fac_facturacion.id_contrato           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_medico               | eq_ref | PRIMARY,id                                                                                                                                                | PRIMARY | 4       | test.adm_ingresos_detalle.id_profesional   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_ingresos            | eq_ref | PRIMARY,FK_adm_ingresos3,FK_adm_ingresos4,FK_adm_ingresos6,FK_adm_ingresos11,FK_adm_ingresos5,FK_adm_ingresos18,FK_adm_ingresos25,FK_adm_ingresos26       | PRIMARY | 4       | test.adm_ingresos_detalle.id_ingreso       |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | adm_estadoingreso       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 1       | test.adm_ingresos.id_est_servicio          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_causa_externa        | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_cau_externa           |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ocupaciones          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_ocupacion             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_medicamento_lote    | eq_ref | PRIMARY,FK_far_medicamento_lote                                                                                                                           | PRIMARY | 4       | test.adm_ingresos_detalle.id_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_medicamentos        | eq_ref | PRIMARY,FK_tb_medicamentos2,FK_tb_medicamentos,FK_tb_medicamentos1                                                                                        | PRIMARY | 4       | test.far_medicamento_lote.id_med           |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_serv_tipo            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_tip_medicamento   |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | far_for_farmaceutica    | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_formafarmaceutica |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | far_med_unidad          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.far_medicamentos.id_unidadmedida      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | adm_usuarios            | eq_ref | PRIMARY,tipo_documento,num_documento,tipo_id,tipo_usuario,FK_tb_identificacion_usuarios21,FK_tb_identificacion_usuarios16,FK_tb_identificacion_usuarios15 | PRIMARY | 4       | test.adm_ingresos.id_usuario               |    1 |   100.00 | Using where                                     |
 
|    1 | SIMPLE      | tb_tipo_poblacion       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_poblacion        |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_barriosveredas       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_barriovereda          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_tipo_documento       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_tipo_doc_usr          |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_municipios           | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_municipio             |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_dx_ing               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_dx_ing_principal      |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_ambitos              | ALL    | PRIMARY                                                                                                                                                   | NULL    | NULL    | NULL                                       |    4 |    75.00 | Using where; Using join buffer (flat, BNL join) |
 
|    1 | SIMPLE      | tb_eps                  | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_eps                   |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_etnias               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_usuarios.id_etnia                 |    1 |   100.00 |                                                 |
 
|    1 | SIMPLE      | tb_regimenes            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY | 4       | test.adm_ingresos.id_regimen               |    1 |   100.00 |                                                 |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------------+------+----------+-------------------------------------------------+
 

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`adm_ingresos`.`id_usuario` AS `id_usuario`,`test`.`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`test`.`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`test`.`adm_usuarios`.`nombre1` AS `nombre1`,`test`.`adm_usuarios`.`nombre2` AS `nombre2`,`test`.`adm_usuarios`.`apellido1` AS `apellido1`,`test`.`adm_usuarios`.`apellido2` AS `apellido2`,`test`.`adm_usuarios`.`sexo` AS `sexo`,`test`.`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`test`.`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`test`.`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`test`.`adm_ingresos`.`direccion` AS `direccion`,`test`.`adm_ingresos`.`telefono` AS `telefono`,`test`.`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`test`.`tb_municipios`.`nom_municipio` AS `nom_municipio`,`test`.`adm_usuarios`.`zona` AS `zona`,`test`.`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`test`.`tb_eps`.`codigo_eps` AS `codigo_eps`,`test`.`tb_eps`.`razon_social` AS `nombre_eps`,`test`.`tb_regimenes`.`descripcion_reg` AS `regimen`,`test`.`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`test`.`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`test`.`tb_medico`.`nombre1`,' ',`test`.`tb_medico`.`nombre2`,' ',`test`.`tb_medico`.`apellido1`,' ',`test`.`tb_medico`.`apellido2`) AS `nom_medico`,`test`.`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`test`.`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`test`.`adm_ingresos`.`id_ambito` AS `id_ambito`,`test`.`tb_ambitos`.`descripcion_ambito` AS `ambito`,`test`.`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`test`.`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`test`.`adm_ingresos`.`num_orden` AS `num_orden`,`test`.`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`test`.`tb_causa_externa`.`descripcion` AS `causa_externa`,`test`.`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`test`.`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`test`.`far_medicamento_lote`.`cum` AS `cum`,`test`.`far_medicamentos`.`atc` AS `atc`,`test`.`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `test`.`tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`test`.`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`test`.`far_medicamentos`.`concentracion` AS `concentracion`,`test`.`far_med_unidad`.`descripcion` AS `unidad_medida`,`test`.`far_for_farmaceutica`.`descripcion` AS `presentacion`,`test`.`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`test`.`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`test`.`fac_facturacion`.`num_factura` AS `num_factura`,`test`.`fac_facturacion`.`estado` AS `estado`,`test`.`fac_facturacion`.`fec_factura` AS `fec_factura`,`test`.`fac_facturacion`.`hor_factura` AS `hor_factura`,`test`.`tb_contratos`.`num_contrato` AS `num_contrato`,`test`.`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`test`.`fac_facturacion_detalle`.`valor` AS `valor`,(`test`.`fac_facturacion_detalle`.`cantidad` * `test`.`fac_facturacion_detalle`.`valor`) AS `valor_total` from `test`.`adm_ingresos_detalle` join `test`.`adm_ingresos` join `test`.`adm_estadoingreso` join `test`.`tb_ocupaciones` join `test`.`tb_ambitos` join `test`.`tb_eps` join `test`.`tb_regimenes` join `test`.`tb_causa_externa` join `test`.`tb_cie10` `tb_dx_ing` join `test`.`adm_usuarios` join `test`.`tb_tipo_documento` join `test`.`tb_barriosveredas` join `test`.`tb_municipios` join `test`.`tb_tipo_poblacion` join `test`.`tb_etnias` join `test`.`seg_usuarios_sistema` `tb_medico` join `test`.`far_medicamento_lote` join `test`.`far_medicamentos` join `test`.`tb_serv_tipo` join `test`.`far_med_unidad` join `test`.`far_for_farmaceutica` left join `test`.`fac_facturacion_detalle` on((`test`.`fac_facturacion_detalle`.`id_ing_detalle` = `test`.`adm_ingresos_detalle`.`id_ing_detalle`)) left join `test`.`fac_facturacion` on(((`test`.`fac_facturacion`.`id_factura` = `test`.`fac_facturacion_detalle`.`id_factura`) and (`test`.`fac_facturacion_detalle`.`id_factura` is not null))) left join `test`.`tb_contratos` on(((`test`.`tb_contratos`.`id_contrato` = `test`.`fac_facturacion`.`id_contrato`) and (`test`.`fac_facturacion`.`id_contrato` is not null))) where ((`test`.`far_for_farmaceutica`.`id_for` = `test`.`far_medicamentos`.`id_formafarmaceutica`) and (`test`.`far_med_unidad`.`id_uni` = `test`.`far_medicamentos`.`id_unidadmedida`) and (`test`.`tb_serv_tipo`.`id_tipo_servicio` = `test`.`far_medicamentos`.`id_tip_medicamento`) and (`test`.`far_medicamentos`.`id_med` = `test`.`far_medicamento_lote`.`id_med`) and (`test`.`far_medicamento_lote`.`id_cum` = `test`.`adm_ingresos_detalle`.`id_medicamento`) and (`test`.`tb_medico`.`id_usuario` = `test`.`adm_ingresos_detalle`.`id_profesional`) and (`test`.`tb_etnias`.`id_etnia` = `test`.`adm_usuarios`.`id_etnia`) and (`test`.`tb_tipo_poblacion`.`id_tipo_pob` = `test`.`adm_usuarios`.`id_tipo_poblacion`) and (`test`.`tb_municipios`.`id_municipio` = `test`.`adm_usuarios`.`id_municipio`) and (`test`.`tb_barriosveredas`.`id_barriovereda` = `test`.`adm_usuarios`.`id_barriovereda`) and (`test`.`tb_tipo_documento`.`id_tipo_doc` = `test`.`adm_usuarios`.`id_tipo_doc_usr`) and (`test`.`adm_usuarios`.`id_usr_salud` = `test`.`adm_ingresos`.`id_usuario`) and (`test`.`tb_dx_ing`.`id_cie` = `test`.`adm_ingresos`.`id_dx_ing_principal`) and (`test`.`tb_causa_externa`.`id_causa_externa` = `test`.`adm_ingresos`.`id_cau_externa`) and (`test`.`tb_regimenes`.`id_regimen` = `test`.`adm_ingresos`.`id_regimen`) and (`test`.`tb_eps`.`id_eps` = `test`.`adm_ingresos`.`id_eps`) and (`test`.`tb_ambitos`.`id_ambito` = `test`.`adm_ingresos`.`id_ambito`) and (`test`.`tb_ocupaciones`.`id_ocupacion` = `test`.`adm_ingresos`.`id_ocupacion`) and (`test`.`adm_estadoingreso`.`id_est_servicio` = `test`.`adm_ingresos`.`id_est_servicio`) and (`test`.`adm_ingresos`.`id_ingreso` = `test`.`adm_ingresos_detalle`.`id_ingreso`) and (`test`.`tb_serv_tipo`.`plano` = 'AM')) |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

optimizer_search_depth=1
0.2 sec

+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------------------------+------+----------+-------------------------------------------------------------------------------+
 
| id   | select_type | table                   | type   | possible_keys                                                                                                                                             | key                       | key_len | ref                                        | rows | filtered | Extra                                                                         |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------------------------+------+----------+-------------------------------------------------------------------------------+
 
|    1 | SIMPLE      | tb_ambitos              | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    4 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | tb_eps                  | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    5 |   100.00 | Using join buffer (flat, BNL join)                                            |
 
|    1 | SIMPLE      | tb_regimenes            | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    5 |   100.00 | Using join buffer (incremental, BNL join)                                     |
 
|    1 | SIMPLE      | tb_etnias               | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    6 |   100.00 | Using join buffer (incremental, BNL join)                                     |
 
|    1 | SIMPLE      | tb_medico               | ALL    | PRIMARY,id                                                                                                                                                | NULL                      | NULL    | NULL                                       |    7 |   100.00 | Using join buffer (incremental, BNL join)                                     |
 
|    1 | SIMPLE      | adm_estadoingreso       | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    8 |   100.00 | Using join buffer (incremental, BNL join)                                     |
 
|    1 | SIMPLE      | tb_tipo_documento       | ALL    | PRIMARY                                                                                                                                                   | NULL                      | NULL    | NULL                                       |    8 |   100.00 | Using join buffer (incremental, BNL join)                                     |
 
|    1 | SIMPLE      | adm_ingresos_detalle    | range  | FK_adm_ingresos_detalle1,FK_adm_ingresos_detalle,FK_adm_ingresos_detalle12                                                                                | FK_adm_ingresos_detalle12 | 5       | NULL                                       |    1 |   100.00 | Using index condition; Using where; Using join buffer (incremental, BNL join) |
 
|    1 | SIMPLE      | adm_ingresos            | eq_ref | PRIMARY,FK_adm_ingresos3,FK_adm_ingresos4,FK_adm_ingresos6,FK_adm_ingresos11,FK_adm_ingresos5,FK_adm_ingresos18,FK_adm_ingresos25,FK_adm_ingresos26       | PRIMARY                   | 4       | test.adm_ingresos_detalle.id_ingreso       |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | tb_causa_externa        | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_ingresos.id_cau_externa           |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | tb_ocupaciones          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_ingresos.id_ocupacion             |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | far_medicamento_lote    | eq_ref | PRIMARY,FK_far_medicamento_lote                                                                                                                           | PRIMARY                   | 4       | test.adm_ingresos_detalle.id_medicamento   |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | far_medicamentos        | eq_ref | PRIMARY,FK_tb_medicamentos2,FK_tb_medicamentos,FK_tb_medicamentos1                                                                                        | PRIMARY                   | 4       | test.far_medicamento_lote.id_med           |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | tb_serv_tipo            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.far_medicamentos.id_tip_medicamento   |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | far_for_farmaceutica    | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.far_medicamentos.id_formafarmaceutica |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | far_med_unidad          | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.far_medicamentos.id_unidadmedida      |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | adm_usuarios            | eq_ref | PRIMARY,tipo_documento,num_documento,tipo_id,tipo_usuario,FK_tb_identificacion_usuarios21,FK_tb_identificacion_usuarios16,FK_tb_identificacion_usuarios15 | PRIMARY                   | 4       | test.adm_ingresos.id_usuario               |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | tb_tipo_poblacion       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_usuarios.id_tipo_poblacion        |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | tb_barriosveredas       | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_usuarios.id_barriovereda          |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | tb_municipios           | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_usuarios.id_municipio             |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | tb_dx_ing               | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.adm_ingresos.id_dx_ing_principal      |    1 |   100.00 |                                                                               |
 
|    1 | SIMPLE      | fac_facturacion_detalle | ALL    | FK_fac_facturacion_detalle1                                                                                                                               | NULL                      | NULL    | NULL                                       |    1 |   100.00 | Using where; Using join buffer (flat, BNL join)                               |
 
|    1 | SIMPLE      | fac_facturacion         | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.fac_facturacion_detalle.id_factura    |    1 |   100.00 | Using where                                                                   |
 
|    1 | SIMPLE      | tb_contratos            | eq_ref | PRIMARY                                                                                                                                                   | PRIMARY                   | 4       | test.fac_facturacion.id_contrato           |    1 |   100.00 | Using where                                                                   |
 
+------+-------------+-------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+--------------------------------------------+------+----------+-------------------------------------------------------------------------------+
 

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`adm_ingresos`.`id_usuario` AS `id_usuario`,`test`.`tb_tipo_documento`.`tipo_doc` AS `tipo_doc`,`test`.`adm_usuarios`.`num_doc_usr` AS `numero_doc`,`test`.`adm_usuarios`.`nombre1` AS `nombre1`,`test`.`adm_usuarios`.`nombre2` AS `nombre2`,`test`.`adm_usuarios`.`apellido1` AS `apellido1`,`test`.`adm_usuarios`.`apellido2` AS `apellido2`,`test`.`adm_usuarios`.`sexo` AS `sexo`,`test`.`adm_usuarios`.`fecha_nacimiento` AS `fecha_nacimiento`,`test`.`adm_ingresos`.`eda_paciente` AS `eda_paciente`,`test`.`adm_usuarios`.`lugar_nacimiento` AS `lugar_nacimiento`,`test`.`adm_ingresos`.`direccion` AS `direccion`,`test`.`adm_ingresos`.`telefono` AS `telefono`,`test`.`tb_barriosveredas`.`nom_barriovereda` AS `nom_barriovereda`,`test`.`tb_municipios`.`nom_municipio` AS `nom_municipio`,`test`.`adm_usuarios`.`zona` AS `zona`,`test`.`tb_ocupaciones`.`nom_ocupacion` AS `nom_ocupacion`,`test`.`tb_eps`.`codigo_eps` AS `codigo_eps`,`test`.`tb_eps`.`razon_social` AS `nombre_eps`,`test`.`tb_regimenes`.`descripcion_reg` AS `regimen`,`test`.`tb_tipo_poblacion`.`descripcion_tipo_pob` AS `tipo_poblacion`,`test`.`tb_etnias`.`nom_etnia` AS `nom_etnia`,concat(`test`.`tb_medico`.`nombre1`,' ',`test`.`tb_medico`.`nombre2`,' ',`test`.`tb_medico`.`apellido1`,' ',`test`.`tb_medico`.`apellido2`) AS `nom_medico`,`test`.`adm_ingresos`.`num_ingreso` AS `num_ingreso`,`test`.`adm_estadoingreso`.`nom_est_servicio` AS `estado_ingreso`,`test`.`adm_ingresos`.`id_ambito` AS `id_ambito`,`test`.`tb_ambitos`.`descripcion_ambito` AS `ambito`,`test`.`adm_ingresos`.`fec_ingreso` AS `fec_ingreso`,`test`.`adm_ingresos`.`hor_ingreso` AS `hor_ingreso`,`test`.`adm_ingresos`.`num_orden` AS `num_orden`,`test`.`tb_causa_externa`.`causa_externa` AS `cod_causa_externa`,`test`.`tb_causa_externa`.`descripcion` AS `causa_externa`,`test`.`tb_dx_ing`.`codigo_cie` AS `dx_ingreso`,`test`.`tb_dx_ing`.`descripcion_cie` AS `nom_dx_ingreso`,`test`.`far_medicamento_lote`.`cum` AS `cum`,`test`.`far_medicamentos`.`atc` AS `atc`,`test`.`far_medicamentos`.`nom_medicamento` AS `nom_medicamento`,(case `test`.`tb_serv_tipo`.`cod_tipo` when '12' then '1' else '2' end) AS `cod_tipo_med`,`test`.`tb_serv_tipo`.`nom_tipo` AS `nom_tipo_med`,`test`.`far_medicamentos`.`concentracion` AS `concentracion`,`test`.`far_med_unidad`.`descripcion` AS `unidad_medida`,`test`.`far_for_farmaceutica`.`descripcion` AS `presentacion`,`test`.`adm_ingresos_detalle`.`fecha` AS `fec_servicio`,`test`.`adm_ingresos_detalle`.`hora` AS `hor_servicio`,`test`.`fac_facturacion`.`num_factura` AS `num_factura`,`test`.`fac_facturacion`.`estado` AS `estado`,`test`.`fac_facturacion`.`fec_factura` AS `fec_factura`,`test`.`fac_facturacion`.`hor_factura` AS `hor_factura`,`test`.`tb_contratos`.`num_contrato` AS `num_contrato`,`test`.`fac_facturacion_detalle`.`cantidad` AS `cantidad`,`test`.`fac_facturacion_detalle`.`valor` AS `valor`,(`test`.`fac_facturacion_detalle`.`cantidad` * `test`.`fac_facturacion_detalle`.`valor`) AS `valor_total` from `test`.`adm_ingresos_detalle` join `test`.`adm_ingresos` join `test`.`adm_estadoingreso` join `test`.`tb_ocupaciones` join `test`.`tb_ambitos` join `test`.`tb_eps` join `test`.`tb_regimenes` join `test`.`tb_causa_externa` join `test`.`tb_cie10` `tb_dx_ing` join `test`.`adm_usuarios` join `test`.`tb_tipo_documento` join `test`.`tb_barriosveredas` join `test`.`tb_municipios` join `test`.`tb_tipo_poblacion` join `test`.`tb_etnias` join `test`.`seg_usuarios_sistema` `tb_medico` join `test`.`far_medicamento_lote` join `test`.`far_medicamentos` join `test`.`tb_serv_tipo` join `test`.`far_med_unidad` join `test`.`far_for_farmaceutica` left join `test`.`fac_facturacion_detalle` on((`test`.`fac_facturacion_detalle`.`id_ing_detalle` = `test`.`adm_ingresos_detalle`.`id_ing_detalle`)) left join `test`.`fac_facturacion` on(((`test`.`fac_facturacion`.`id_factura` = `test`.`fac_facturacion_detalle`.`id_factura`) and (`test`.`fac_facturacion_detalle`.`id_factura` is not null))) left join `test`.`tb_contratos` on(((`test`.`tb_contratos`.`id_contrato` = `test`.`fac_facturacion`.`id_contrato`) and (`test`.`fac_facturacion`.`id_contrato` is not null))) where ((`test`.`far_for_farmaceutica`.`id_for` = `test`.`far_medicamentos`.`id_formafarmaceutica`) and (`test`.`far_med_unidad`.`id_uni` = `test`.`far_medicamentos`.`id_unidadmedida`) and (`test`.`tb_serv_tipo`.`id_tipo_servicio` = `test`.`far_medicamentos`.`id_tip_medicamento`) and (`test`.`far_medicamentos`.`id_med` = `test`.`far_medicamento_lote`.`id_med`) and (`test`.`far_medicamento_lote`.`id_cum` = `test`.`adm_ingresos_detalle`.`id_medicamento`) and (`test`.`adm_ingresos_detalle`.`id_profesional` = `test`.`tb_medico`.`id_usuario`) and (`test`.`adm_usuarios`.`id_etnia` = `test`.`tb_etnias`.`id_etnia`) and (`test`.`tb_tipo_poblacion`.`id_tipo_pob` = `test`.`adm_usuarios`.`id_tipo_poblacion`) and (`test`.`tb_municipios`.`id_municipio` = `test`.`adm_usuarios`.`id_municipio`) and (`test`.`tb_barriosveredas`.`id_barriovereda` = `test`.`adm_usuarios`.`id_barriovereda`) and (`test`.`adm_usuarios`.`id_tipo_doc_usr` = `test`.`tb_tipo_documento`.`id_tipo_doc`) and (`test`.`adm_usuarios`.`id_usr_salud` = `test`.`adm_ingresos`.`id_usuario`) and (`test`.`tb_dx_ing`.`id_cie` = `test`.`adm_ingresos`.`id_dx_ing_principal`) and (`test`.`tb_causa_externa`.`id_causa_externa` = `test`.`adm_ingresos`.`id_cau_externa`) and (`test`.`adm_ingresos`.`id_regimen` = `test`.`tb_regimenes`.`id_regimen`) and (`test`.`adm_ingresos`.`id_eps` = `test`.`tb_eps`.`id_eps`) and (`test`.`adm_ingresos`.`id_ambito` = `test`.`tb_ambitos`.`id_ambito`) and (`test`.`tb_ocupaciones`.`id_ocupacion` = `test`.`adm_ingresos`.`id_ocupacion`) and (`test`.`adm_ingresos`.`id_est_servicio` = `test`.`adm_estadoingreso`.`id_est_servicio`) and (`test`.`adm_ingresos`.`id_ingreso` = `test`.`adm_ingresos_detalle`.`id_ingreso`) and (`test`.`tb_serv_tipo`.`plano` = 'AM')) |
 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Sergei Petrunia [ 2016-01-24 ]

I get even faster result with @@optimizer_search_depth=1 (0.00, 0.01, 0.02 sec - basically, it's instant).

The bug seems to be about join optimizer enumerating too many plans by default.

I guess that MySQL 5.6 is not affected, because it has a special optimization to speed-up planning for the case when there are many eq_ref tables. I can't find the name of that optimization in the maula, but here are some pointers:
https://github.com/mysql/mysql-server/commit/a0372c4112c5fa3567ca4368b060693ba92d1722 , also search for eq_ref_extension_by_limited_search.

igor also mentioned he has looked at that optimization in detail and was against back-porting it. Will need to discuss this with him.

Comment by Sergei Petrunia [ 2016-01-27 ]

Experimenting with MySQL 5.6.27, debug binary:

The default settings are:
optimizer_search_depth=62
optimizer_prune_level=1

search_depth=62 means that exhausive search is used. prune_level=1 means
pruning is ON.

with optimizer_prune_level=0:

search_depth=2, time= 0.02 sec
search_depth=4, time= 0.08 sec
search_depth=6, time= 2.80 sec
search_depth=8, time= 116 sec
...

with optimizer_prune_level=1, EXPLAIN execution time doesn't depend on
optimizer_search_depth and is around 0.01 - 0.02 sec

Comment by Sergei Petrunia [ 2016-01-27 ]

A blog mentioning the optimization in mysql-5.6: http://jorgenloland.blogspot.ru/2012/04/improvements-for-many-table-joins-in.html

Comment by Sergei Petrunia [ 2016-01-27 ]

The default settings in MariaDB 10.1.10 are the same:

  optimizer_search_depth=62
 
  optimizer_prune_level=1

Time to run EXPLAIN:

with optimizer_prune_level=1:

optimizer_search_depth=4: 0.02 sec
 
optimizer_search_depth=8: 1.40 sec
 
optimizer_search_depth=10: 12.0 sec

with optimizer_prune_level=0:

optimizer_search_depth=4: 0.08 sec
 
optimizer_search_depth=8: 63.7 sec
 
optimizer_search_depth=10: (didn't finish in 1200 sec)

Comment by Sergei Petrunia [ 2016-02-19 ]

Discussed with igor.

  • Need to study MySQL's solution
  • A guess (or attempt to remember ) how it works: they seem to find groups of eq_ref tables and replace them with one element.
    • Then, join optimization avoids enumerating lots of join orders with equvalent cost.
  • If we port this to MariaDB, we need to take condition selectivity into account.
Comment by Sergei Petrunia [ 2016-02-19 ]

This is a relatively big feature, it cannot be fixed in 10.0 or 10.1. Changing FixVersion to 10.2

Comment by Alexander Ceballos [ 2016-04-25 ]

I was testing MariaDB 10.2 with my views and I found something, the query in the view does not work when I uses * for the columns, but if I specify the columns works, for example this query: SELECT id_usuario,fec_factura FROM vista_rip_consulta worked.

I think is the same case for above versions.

Comment by Alexander Ceballos [ 2017-06-24 ]

I was testing maria 10.3 and the problem mentioned above improves using the clause WHERE. Then the queries take less time to execute.

Generated at Thu Feb 08 07:34:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.