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:
None 
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
Description:
I was used to access db by Store Procedures with mysql server 5.7. Now I'm running in MySql 8.0 and the store procedures seem are not using any index nor primary key. My tables have 1.5 million rows and when I run the same query inside the store procedure it cost 300 or 400 seconds, but outside the SP only 4 seconds

How to repeat:
Running any store procedure and the same query outside the SP
[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".