All geek questions in one place

SQL query optimization in MySQL

I would like to know why this query takes slowly (about 10-20 seconds), the three tables used contain 500,000 records, this is a query:

SELECT  *, 'rg_egresos' AS nombre_tabla
    FROM  rg_detallexml DE
    INNER JOIN  rg_egresos EG
    INNER JOIN  rg_emisor EM  ON DE.idContador = EG.id
      AND  DE.idDetalleXml = EG.idDetalleXml
      AND  DE.idContador = EM.idContador
      AND  DE.idDetalleXml = EM.idDetalleXml
    WHERE  DE.idContador = '14894'
      AND  DATE_FORMAT(dateFechaHora, '%Y-%m-%d') BETWEEN '2017-10-01'
                                                      AND '2017-10-31'
      AND  strTipodeComprobante = 'egreso'
      AND  version_xml = '3.2'
      AND  estado_factura = 0
      AND  modificado = 0;

And this is what he shows when I use EXPLAIN

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: EG
         type: index_merge
possible_keys: idx_idDetallexml,idx_estado_factura,idx_modificado,idx_idContador
          key: idx_idContador,idx_estado_factura,idx_modificado
      key_len: 4,4,4
          ref: NULL
         rows: 2111
        Extra: Using intersect(idx_idContador,idx_estado_factura,idx_modificado); Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: DE
         type: eq_ref
possible_keys: PRIMARY,idx_strTipodeComprobante,idx_idContador,idx_version_xml
          key: PRIMARY
      key_len: 4
          ref: db_pwf.EG.idDetalleXml
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: EM
         type: ref
possible_keys: idx_idContador,idx_idDetallexml
          key: idx_idDetallexml
      key_len: 4
          ref: db_pwf.DE.idDetalleXml
         rows: 1
        Extra: Using where

Can you see a way to improve the query? I have other queries that work with large tables, and they are faster, all the necessary fields have their own index, thanks.

Rg_detallexml table:

+---------------------------------+--------------+------+-----+---------+----------------+
| Field                           | Type         | Null | Key | Default | Extra          |
+---------------------------------+--------------+------+-----+---------+----------------+
| idDetalleXml                    | int(10)      | NO   | PRI | NULL    | auto_increment |
| UUID                            | varchar(50)  | NO   | MUL | NULL    |                |
| dateFechaSubida                 | varchar(7)   | YES  |     | NULL    |                |
| idContador                      | int(10)      | NO   | MUL | NULL    |                |
| dateFechaHora                   | datetime     | YES  | MUL | NULL    |                |
| dateFechaHoraCertificacion      | datetime     | YES  |     | NULL    |                |
| dateFechaPago                   | datetime     | YES  |     | NULL    |                |
| intFolio                        | int(10)      | YES  |     | NULL    |                |
| strSerie                        | varchar(2)   | YES  |     | A       |                |
| doubleDescuento                 | double       | YES  |     | NULL    |                |
| doubleTotal                     | double       | YES  |     | NULL    |                |
| doubleSubtotal                  | double       | YES  |     | NULL    |                |
| duobleTotalImpuestosTrasladados | double       | YES  |     | NULL    |                |
| doubleTotalImpuestosRetenidos   | double       | YES  |     | NULL    |                |
| doubleTotalRetencionesLocales   | double       | YES  |     | NULL    |                |
| doubleTotalTrasladosLocales     | double       | YES  |     | NULL    |                |
| strTipodeComprobante            | varchar(15)  | YES  | MUL | NULL    |                |
| strMetodoDePago                 | varchar(150) | YES  |     | NULL    |                |
| strFormaDePago                  | varchar(150) | YES  |     | NULL    |                |
| strMoneda                       | varchar(10)  | YES  |     | NULL    |                |
| tipoCambio                      | double       | NO   |     | NULL    |                |
| strLugarExpedicion              | varchar(150) | YES  |     | NULL    |                |
| DIOT                            | int(1)       | YES  |     | 0       |                |
| version_xml                     | varchar(10)  | NO   | MUL | NULL    |                |
+---------------------------------+--------------+------+-----+---------+----------------+

Rg_egresos table:

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| id_egreso                 | int(11)      | NO   | PRI | NULL    | auto_increment |
| id                        | int(11)      | NO   | MUL | NULL    |                |
| idDetalleXml              | int(10)      | NO   | MUL | NULL    |                |
| idCatalogo                | int(19)      | NO   | MUL | NULL    |                |
| tipoCuenta                | int(11)      | NO   | MUL | NULL    |                |
| intRubro                  | int(1)       | NO   |     | NULL    |                |
| RFC                       | varchar(20)  | NO   | MUL | NULL    |                |
| compra_gastos_0_porciento | float        | NO   | MUL | NULL    |                |
| deducible                 | int(1)       | NO   |     | NULL    |                |
| compra_gastos_exentos     | float        | NO   |     | NULL    |                |
| no_deducibles             | float        | NO   |     | NULL    |                |
| estado_factura            | int(11)      | NO   | MUL | NULL    |                |
| fecha                     | date         | NO   | MUL | NULL    |                |
| total_xml                 | double       | NO   |     | NULL    |                |
| subtotal_xml              | double       | NO   |     | NULL    |                |
| iva_xml                   | double       | NO   |     | NULL    |                |
| total_impuestos           | double       | NO   |     | NULL    |                |
| abonado                   | double       | NO   |     | NULL    |                |
| subtotal                  | double       | NO   |     | NULL    |                |
| iva                       | double       | NO   |     | NULL    |                |
| pendiente                 | double       | NO   |     | NULL    |                |
| subtotal_sin_iva          | double       | NO   |     | NULL    |                |
| acreditable               | int(1)       | NO   | MUL | 0       |                |
| fecha_operacion           | datetime     | NO   | MUL | NULL    |                |
| modificado                | int(1)       | NO   | MUL | NULL    |                |
| UUID                      | varchar(50)  | NO   | MUL | NULL    |                |
| IEPS                      | double       | NO   |     | NULL    |                |
| retencion_iva             | double       | NO   |     | NULL    |                |
| retencion_isr             | double       | NO   |     | NULL    |                |
| imp_local                 | double       | NO   |     | 0       |                |
| enviado_a                 | int(11)      | NO   | MUL | NULL    |                |
| enviado_al_iva            | int(1)       | NO   |     | NULL    |                |
| EsNomina                  | int(1)       | NO   | MUL | 0       |                |
| dateFechaPago             | date         | NO   | MUL | NULL    |                |
| nota_credito              | int(1)       | NO   | MUL | NULL    |                |
| extranjero                | int(1)       | NO   | MUL | NULL    |                |
| pago_banco                | int(1)       | NO   | MUL | NULL    |                |
| idBanco_Pago              | int(20)      | NO   | MUL | NULL    |                |
| movimientoPago            | int(10)      | NO   |     | NULL    |                |
| saldo_banco               | varchar(50)  | NO   |     | NULL    |                |
| tipo_pago                 | int(1)       | NO   |     | 0       |                |
| responsable               | varchar(100) | NO   |     | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+

Rg_emisor table:

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| idEmisor        | int(10)      | NO   | PRI | NULL    | auto_increment |
| idDetalleXml    | int(10)      | NO   | MUL | NULL    |                |
| idContador      | int(10)      | NO   | MUL | NULL    |                |
| strRFC          | varchar(13)  | NO   |     | NULL    |                |
| strNombreEmisor | varchar(200) | YES  |     | NULL    |                |
| strRegimen      | varchar(250) | YES  |     | NULL    |                |
| strPais         | varchar(40)  | YES  |     | MX      |                |
| strEstado       | varchar(50)  | YES  |     | NULL    |                |
| intCP           | int(5)       | YES  |     | NULL    |                |
| strMunicipio    | varchar(250) | YES  |     | NULL    |                |
| strLocalidad    | varchar(250) | YES  |     | NULL    |                |
| strColonia      | varchar(250) | YES  |     | NULL    |                |
| intNumExt       | int(10)      | YES  |     | NULL    |                |
| intNumInt       | int(10)      | YES  |     | NULL    |                |
| strCalle        | varchar(250) | YES  |     | NULL    |                |
| regimenFiscal   | varchar(20)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
+4
source share
3 answers

Now that you have shown the tables, we see that rg_egresos.idit is not a table identifier. Therefore, there may be several entries for one contador in the table. Let's look at the tables and query in more detail:

contador DetalleXml. . , rg_detallexml . , idDetalleXml, rg_egresos rg_emisors.

. rg_detallexml, , , rg_detallexml . , (, - / ). rg_egresos rg_emisors rg_detallexml/contador , rg_egresos rg_emisors , .

: rg_detallexml.

create index idx_de on rg_detallexml(idcontador, strtipodecomprobante, version_xml,
                                     datefechahora, iddetallexml);

rg_egresos:

create index idx_eg on rg_egresos(id, iddetallexml, estado_factura, modificad);

, rg_emisor:

create index idx_em on rg_emisor(idcontador, iddetallexml);

, , , . rg_detallexml , . :

create index idx_eg2 on rg_egresos(id, estado_factura, modificad, iddetallexml);

contador idDetalleXml.

+3

, :

DATE_FORMAT(dateFechaHora, '%Y-%m-%d') BETWEEN '2017-10-01' AND '2017-10-31'

dateFechaHora datetime? datetime (DATE_FORMAT)? dateFechaHora, .

:

and DateFechaHora >= '2017-10-01' and DateFechaHora < '2017-11-01'
                                                       ^^^^^^^^^^

, , .

, :

select
  *,
  'rg_egresos' AS nombre_tabla 
from
  rg_detallexml DE inner join rg_egresos EG
  on DE.idContador = EG.id and DE.idDetalleXml = EG.idDetalleXml 
  inner join rg_emisor EM on DE.idContador = EM.idContador 
  and DE.idDetalleXml = EM.idDetalleXml 
where
  DE.idContador = '14894' 
  and dateFechaHora >= '2017-10-01' and dateFechaHora < '2017-11-01' 
  and strTipodeComprobante = 'egreso'
  and version_xml = '3.2' 
  and estado_factura = 0 
  and modificado = 0
;
+1

. .

  AND  DATE_FORMAT(dateFechaHora, '%Y-%m-%d') BETWEEN '2017-10-01'
                                                  AND '2017-10-31'


  AND DE.dateFechaHora >= '2017-10-01'
  AND DE.dateFechaHora  < '2017-10-01' + INTERVAL 1 MONTH

DE - :

DE:  INDEX(idContador, strTipodeComprobante, version_xml, dateFechaHora)
       -- date last; others in any order

EG - :

EG:  INDEX(estado_factura, modificado, id)   -- in any order
DE:  INDEX(idContador, idDetalleXml,
           strTipodeComprobante, version_xml, dateFechaHora)


EM:  INDEX(idContador, idDetalleXml)   -- in either order

Using intersections is almost always the key to having a composite index instead of individual indexes. (Separate indexes may be useful for other queries.)

(That is, add all of these indexes, and then decide the Optimizer.)

Use SHOW CREATE TABLE, not less descriptive DESCRIBE.

Do you really need SELECT *?

Request, after my suggestions:

SELECT  DE.*,
        EG.*,
        EM.*,
        'rg_egresos' AS nombre_tabla
    FROM  rg_detallexml DE
    INNER JOIN  rg_egresos EG
       ON  DE.idContador = EG.id
      AND  DE.idDetalleXml = EG.idDetalleXml
    INNER JOIN  rg_emisor EM
       ON  DE.idContador = EM.idContador
      AND  DE.idDetalleXml = EM.idDetalleXml
    WHERE  DE.idContador = '14894'
      AND  DE.dateFechaHora >= '2017-10-01'
      AND  DE.dateFechaHora  < '2017-10-01' + INTERVAL 1 MONTH
      AND  DE.strTipodeComprobante = 'egreso'
      AND  DE.version_xml = '3.2'
      AND  EG.estado_factura = 0
      AND  EG.modificado = 0;
+1
source

Source: https://habr.com/ru/post/1694880/

More articles:


All Articles