Bug #92808 | Store procedures are not using indexes or PK | ||
---|---|---|---|
Submitted: | 16 Oct 2018 21:27 | Modified: | 19 Nov 2018 13:18 |
Reporter: | Guadalupe María Delorme | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 8.0 | OS: | Windows |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX, primary key, Store Procedure |
[16 Oct 2018 21:27]
Guadalupe María Delorme
[16 Oct 2018 21:33]
MySQL Verification Team
Thank you for the bug report. Please provide a repeatable test case. Thanks.
[17 Oct 2018 21:13]
Guadalupe María Delorme
17:49:04 CALL calcularTotalAnualPorFamilia(2017, '45100019') 1 row(s) returned 217.875 sec / 0.000 sec Above is the execution of the SP. Below are the execution of the same querys outside the SP 17:58:30 DROP temporary table if exists tmp_total_familias 0 row(s) affected 0.000 sec 17:58:30 CREATE TEMPORARY TABLE tmp_total_familias (Grupo CHAR(3), importe DOUBLE) 0 row(s) affected 0.000 sec 17:58:30 INSERT into tmp_total_familias SELECT MO.Grupo, sum(cantidad * coalesce(MO.precio , 0)) AS importe from detallefacturas DF join facturas F ON DF.IdFactura = F.IdFactura JOIN materiales MA ON DF.IdMaterial = MA.IdMaterial JOIN modelos MO ON MA.IdModelo = MO.IdModelo where F.IdCliente = '45100019' and DF.Anio = 2017 GROUP BY MO.Grupo 7 row(s) affected Records: 7 Duplicates: 0 Warnings: 0 1.109 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'MS' 1 row(s) returned 0.000 sec / 0.000 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'FS' 1 row(s) returned 0.000 sec / 0.000 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'OPT' 1 row(s) returned 0.000 sec / 0.000 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'ELE' 1 row(s) returned 0.000 sec / 0.000 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'BAT' 1 row(s) returned 0.000 sec / 0.000 sec 17:58:31 SELECT importe FROM tmp_total_familias Where Grupo = 'RE' 1 row(s) returned 0.000 sec / 0.000 sec Here is the SP CREATE DEFINER=`root`@`localhost` PROCEDURE `calcularTotalAnualPorFamilia`(pAnio SMALLINT, pCliente CHAR(8)) BEGIN DECLARE totalMs DOUBLE; DECLARE totalFs DOUBLE; DECLARE totalOpt DOUBLE; DECLARE totalEle DOUBLE; DECLARE totalBat DOUBLE; DECLARE totalRe DOUBLE; DROP temporary table if exists tmp_total_familias ; CREATE TEMPORARY TABLE tmp_total_familias (Grupo CHAR(3), importe DOUBLE); INSERT into tmp_total_familias SELECT MO.Grupo, sum(cantidad * coalesce(MO.precio , 0)) AS importe from detallefacturas DF join facturas F ON DF.IdFactura = F.IdFactura JOIN materiales MA ON DF.IdMaterial = MA.IdMaterial JOIN modelos MO ON MA.IdModelo = MO.IdModelo where F.IdCliente = pCliente and DF.Anio = pAnio GROUP BY MO.Grupo; SET totalMS = (SELECT importe FROM tmp_total_familias Where Grupo = 'MS'); SET totalFS = (SELECT importe FROM tmp_total_familias Where Grupo = 'FS'); SET totalOpt = (SELECT importe FROM tmp_total_familias Where Grupo = 'OPT'); SET totalEle = (SELECT importe FROM tmp_total_familias Where Grupo = 'ELE'); SET totalBat = (SELECT importe FROM tmp_total_familias Where Grupo = 'BAT'); SET totalRe = (SELECT importe FROM tmp_total_familias Where Grupo = 'RE'); SELECT totalMs, totalFs, totalOpt, TotalEle, TotalBat, TotalRe; END
[18 Oct 2018 12:50]
MySQL Verification Team
Hi, Thank you for your bug report. Based on what you have sent us, we can not conclude what is the problem. First of all, all these SELECT's from the temporary table can not utilise any indices, because you have not defined any on that temporary table. Second, that table has only seven rows, so no indices could be possibly used on such a small table. If you are , however, writing about SELECT from INSERT ... SELECT command, we can not help you with that either. That is because we need to see the EXPLAIN EXTENDED on that SELECT. If EXPLAIN shows that no indices were used then we need the dumps of all tables involved. If the size is the problem, make a dump with enough rows to repeat the problem.
[19 Nov 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".