MySQL - Fast query but slow Stored Procedures

Question

I know this question has been discussed quite a lot here. But I have a particular case when I need to pass a list of parameters (comma - separated) which prevents me to have a local variable declared and used for input parameter.

As pointed out in the above discussion, it is suggested to declare a local variable and assign the parameters to this variable. However, what should I do in case my parameter is of type Text and can be comma - separated list?

For example -

CREATE DEFINER=`Admin`@`%` PROCEDURE `MyReport`(
    p_myparameter_HK Text
)
BEGIN
SELECT
*
FROM MyTable
WHERE 
(find_in_set(MyTable.column_HK, p_myparameter_HK) <> 0 OR MyTable.column_HK IS NULL)
;

END

Performance:

Query

If I just run the query - 300 ms

Stored Procedure

CALL MyReport('0000_abcd_fake_000')

This procedure keeps running endlessly.

My question is, how can I disable parameter sniffling and use local variable instead of find_in_set to match the query performance.

It is usually not useful to reference a non-MySQL reference when discussing a MySQL topic. There are _many_ differences. (MariaDB and Percona are OK.) — Rick James, Jun 29 '19 at 00:38
@RickJames But I need that for condition when the value doesn't exist. — Hello.World, Jun 29 '19 at 01:02
@RickJames The full query produces about 3-3.5 Million rows. The main table has about 1 million rows. — Hello.World, Jun 29 '19 at 01:04
I'm confused -- How can the query produce more rows than the table? Have you left some critical parts out? — Rick James, Jun 29 '19 at 01:04
The source table has value X (say). Now this `X` can be in multiple languages. So when I join those it basically produces all records with `X` and it's language of origin. The data is actually of a User and his/her exams. The Users can appear in one or multiple languages for a test. — Hello.World, Jun 29 '19 at 01:07

score 1 · Accepted Answer · answered Jun 29 '19 at 00:43

1

The times that I have needed to pass an arbitrary list of things to a Stored Procedure, I did it this way:

CREATE (or already have) a TABLE for passing the info in. Both the caller and the Procedure know the name of the procedure. (Or it could be passed in, but adds some messy "prepare-executes".)
Do a bulk INSERT into that table. (INSERT INTO tbl (a,b) VALUES (...), (..), ...;)
Perform JOINs or whatever to use the table efficiently.

In my case, the extra effort was worth it.

answered Jun 29 '19 at 00:43

Rick James

135,179
13
127
222

Are you saying that I need go create a temporary table with parameter values and then join with the result set? – Hello.World Jun 29 '19 at 00:44
@Hello.World - Is there an index on `column_HK`? What is its datatype? (Or simply provide `SHOW CREATE TABLE`.) – Rick James Jun 29 '19 at 01:04
The HK is basically SHA2(column, 256). Also, all _HKs have indexes. – Hello.World Jun 29 '19 at 01:05
@Hello.World - That gets into a different dimension -- How big (gigabytes) is the table? How big is `innodb_buffer_pool_size`? How much RAM? – Rick James Jun 29 '19 at 01:06
@Hello.World - And why are you using hashes? Are the things being hashed big text/blob columns? – Rick James Jun 29 '19 at 01:07
innodb_buffer_pool_size is 2.5G. RAM is 16GB. Hash keys are primary keys for warehouse tables that I am using. And their datatype is varchar(36) – Hello.World Jun 29 '19 at 01:09
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/195726/discussion-between-hello-world-and-rick-james). – Hello.World Jun 29 '19 at 01:09

MySQL - Fast query but slow Stored Procedures

1 Answers1