How can I optimize this MySQL query in a stored procedure?

Question

SET @count := (SELECT count(*)
               FROM dbo.ManyToManyTable SRSG
                        JOIN tableA SG on SRSG.a = SG.a
                        JOIN tableB SR on SRSG.b = SR.a
               WHERE SR.c = INPUT_VALUE);

IF (@count > 0)
THEN
    SELECT SG.fieldA, SG.fieldB
    FROM dbo.ManyToManyTable SRSG
             JOIN tableA SG on SRSG.a = SG.a
             JOIN tableB SR on SRSG.b = SR.a
    WHERE SR.c = INPUT_VALUE;
ELSE
    SELECT SG.a, SG.b FROM dbo.tableA SG WHERE SG.b = "default value";
END IF;

It's for a MySQL database. This works for me, but I don't like the duplicate select query. However, I have no idea how to fix it under the constraint which is the logic has be within one stored procedure.

I tried to use SET @var but I can't get it to work. Is that what you're thinking? — Liam.Nguyen, Jul 29 '21 at 21:42

score 0 · Answer 1 · answered Jul 29 '21 at 21:41

0

You could write this as a single query:

with cte as (
      SELECT SG.fieldA, SG.fieldB
      FROM dbo.ManyToManyTable SRSG JOIN
           tableA SG
           on SRSG.salesGroupId = SG.salesGroupId JOIN
           tableB SR 
           on SRSG.salesRegionId = SR.salesRegionId
     WHERE SR.AField = INPUT_VALUE
    )
select *
from cte
union all
select SG.a, SG.b 
from dbo.tableA SG 
where SG.b = 'default value' and
      not exists (select 1 from cte);

MySQL tends to materialize CTEs (always?), so this should run the query once rather than twice.

answered Jul 29 '21 at 21:41

Gordon Linoff

1,242,037
58
646
786

Unfortunately, it looks like MySQL doesn't support cte according to https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql since I keep getting syntax error. However, MYSQL doc does mention about WITH. Weird! – Liam.Nguyen Jul 29 '21 at 22:20
@Liam.Nguyen . . . MySQL has supported CTEs since version 8 was released over three years ago. – Gordon Linoff Jul 29 '21 at 22:39

score 0 · Answer 2 · answered Jul 30 '21 at 15:56

A quick, but partial speedup is to replace

SET @count = ( SELECT COUNT(*) ... );
IF (@count > 0)

with this (using the rest of that initial SELECT):

IF ( EXISTS ( SELECT 1 FROM ... ) )

Also for performance, be sure to have in SRSG:

PRIMARY KEY(a,b)
INDEX(b,a)

and don't bother with an AUTO_INCREMENT.

How can I optimize this MySQL query in a stored procedure?

2 Answers2