Reputation: 185
SQL query in MySQL is taking too long
I have a problem with one query, this query is taking too long to find the records, sometimes it takes too long sending the data and sometimes sorting the results I'm talking about 10 to 20 seconds, this table isn't super big, I have bigger tables with millions of records, this table is small comparing to them
SELECT idFactura,
strNombre,
intFolio,
CH_razon_social AS strRazonSocial,
CH_nombre_comercial AS strNombreComercial,
CH_RFC AS strRFC,
doubleTotal,
intTimbrada,
intCancelada,
dateFechaHora,
intStatus,
strSerie,
email_enviado,
strFolioFiscal,
numero_abonos,
saldoInsoluto_pagos,
tipo_comprobante,
strSerieParc
FROM tbl_factura2
WHERE dateFechaHora >= '2018-05-06'
AND dateFechaHora <= '2018-06-06'
AND intTimbrada = 1
AND intCancelada = 0
AND cfdi_33 = 1
AND RFC_usuario = 'NUSN900420SS5'
AND numero_abonos = 0
AND (strFormaPago = 'PPD'
OR strMetodoPago = '99')
ORDER
BY idFactura DESC;
when I use the command EXPLAIN this is what I get and you can see that it is taking the idx_compuesto7 index but sometimes it takes another, I know this depends on what MySQL is better for the query, I would like to know what can I change or another way to do this query to get a better performance, I know that the OR in the query could be a problem but I tried to do the query as two different to remove the OR but I got the same problem.
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_factura2
type: ref
possible_keys: idx_fecha,idx_timbrada,idx_cancelada,idx_formaPago,idx_cfdi33,idx_rfc_usuario,idx_strMetodoPago,idx_compuesto3,idx_compuesto4,idx_compuesto5,idx_compuesto6,idx_compuesto7,idx_compuesto8,idx_compuesto9,idx_compuesto10,idx_compuesto11,idx_compuesto12
key: idx_compuesto7
key_len: 52
ref: const
rows: 40
Extra: Using where; Using filesort
as you can see the possible numbers of row are 40 records but with this query when I run it I just get 1 record, I don't know if this is acceptable or not considering the total number of records of the whole table
This is the definition of the whole indexes of this table
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_factura2 | 0 | PRIMARY | 1 | idFactura | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_contador | 1 | idContador | A | 2248 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_fecha | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_timbrada | 1 | intTimbrada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_cancelada | 1 | intCancelada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_contadorSub | 1 | idContadorSub | A | 131 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_folio | 1 | intFolio | A | 1578 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_formaPago | 1 | strFormaPago | A | 13 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_email_enviado | 1 | email_enviado | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_cfdi33 | 1 | cfdi_33 | A | 59 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_status | 1 | intStatus | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_serie | 1 | strSerie | A | 538 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_rfc_usuario | 1 | RFC_usuario | A | 17991 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_facturaexcel | 1 | facturaExcel | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_razonSocial | 1 | CH_razon_social | A | 33734 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_nombreComercial | 1 | CH_nombre_comercial | A | 2620 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_RFC | 1 | CH_RFC | A | 67469 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_folio_fiscal | 1 | strFolioFiscal | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_saldoInsoluto | 1 | saldoInsoluto_pagos | A | 7 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_tipo_comprobante | 1 | tipo_comprobante | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_existe_facprincipal | 1 | existe_facturaPrinc_pagos | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_strMetodoPago | 1 | strMetodoPago | A | 7 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 1 | idFactura | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 2 | idContador | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 3 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto2 | 1 | idContador | A | 2306 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto2 | 2 | cfdi_33 | A | 3551 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 5 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 1 | RFC_usuario | A | 4819 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 2 | intTimbrada | A | 5997 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 3 | intCancelada | A | 8705 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 4 | cfdi_33 | A | 11244 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 2 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 1 | RFC_usuario | A | 3551 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 1 | RFC_usuario | A | 8995 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 7 | saldoInsoluto_pagos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 8 | tipo_comprobante | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 2 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 6 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 7 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 5 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 6 | strFormaPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 7 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 5 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 6 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 7 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 1 | RFC_usuario | A | 3696 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 7 | strFormaPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 1 | RFC_usuario | A | 12851 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 7 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
For me the query is not taking the proper index or maybe I need to make another composite index to fix this.
This is the number of distinct values of the fields used in the query if this info is useful for someone, do you see something wrong with the info and the way the query was made? I hope you can help me to improve this, thank you.
*************************** 1. row ***************************
count(distinct dateFechaHora): 260117
count(distinct intTimbrada): 2
count(distinct intCancelada): 2
count(distinct cfdi_33): 2
count(distinct RFC_usuario): 3621
count(distinct numero_abonos): 9
count(distinct strFormaPago): 19
count(distinct strMetodoPago): 161
total_records: 263320
Upvotes: 0
Views: 403
Answers (1)
Reputation: 142208
Step 1: Get rid of any index that has columns after dateFechaHora. When there is a range in the WHERE, the use of the index stops after testing for that column.
Step 2: Get rid of INDEX(a) if you have INDEX(a,b). Extend this 'rule' to any prefix.
For this particular query, here is the optimal index:
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, -- in any order
dateFechaHora) -- last
See Index Cookbook .
As for removing the OR, this may help:
(
SELECT ...
FROM tbl_factura2
WHERE ...
AND strFormaPago = 'PPD'
)
UNION DISTINCT
(
SELECT ...
FROM tbl_factura2
WHERE ...
AND strMetodoPago = '99'
)
ORDER BY idFactura DESC;
plus these two indexes:
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, strFormaPago,
dateFechaHora) -- last
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, strMetodoPago,
dateFechaHora) -- last
As a rule: If you have an OR getting in the way of operformance,
- Make 2
SELECTs, putting one side of theORin each; - Remove
ORDER BYif present. (LIMITis more complex; not covered here.) - Connect the
SELECTsbyUNION ALLif you are sure there is no overlap, orUNION DISTINCT(slower) if there could be dups. - Use parens so that when you tack
ORDER BYonto the end, it clearly belongs to theUNION, not the lastSELECT. - Make 2 new indexes according to the same rules in my Cookbook, one aimed at each
SELECT.
But... UNION has some overhead, so it may or may not speed up the query. (I guess that it won't be beneficial for this query.)
Upvotes: 1
Related Questions
- Following query taking too long to execute. How to optimize it
- MySQL Slow Query Optimisation
- MySQL query is slow to run. Help optimize it
- Need to optimize SQL Query - Taking lot a of time for execution
- mysql query taking more than 30 seconds
- Query is taking too much time .and impacting performance
- Why is my MySQL query is so slow?
- Mysql query optimization - its really slow
- Query is taking too much time
- MySql Query very slow