Stored Procedure slower than equivalent query

Question

Why is it that this stored procedure:

CREATE
    DEFINER = mysql_user@`%` PROCEDURE sp_GetSessionData(IN SessionId varchar(45))
BEGIN
    SELECT * FROM sessiondata WHERE SessionGuid = SessionId;
END;

Takes 5 minutes to run but this equivalent query:

SELECT * FROM sessiondata WHERE SessionGuid = 'd2c26d6d-4e80-4e80-4e80-2d7ca9cfa3a3';

Only takes 40ms to run?

I have an index on the SessionGuid column and the column is of type varchar(45) which is the exact same as the input parameter for my stored procedure.

UPDATE:

Here is what I get when I put an EXPLAIN in the stored procedure:

╔════╦═════════════╦══════════╦════════════╦══════╦═══════════════╦══════╦═════════╦══════╦══════════╦══════════╦═════════════╗
║ id ║ select_type ║  table   ║ partitions ║ type ║ possible_keys ║ key  ║ key_len ║ ref  ║   rows   ║ filtered ║    Extra    ║
╠════╬═════════════╬══════════╬════════════╬══════╬═══════════════╬══════╬═════════╬══════╬══════════╬══════════╬═════════════╣
║  1 ║ SIMPLE      ║ perfinfo ║ NULL       ║ ALL  ║ NULL          ║ NULL ║ NULL    ║ NULL ║ 77249445 ║      100 ║ Using where ║
╚════╩═════════════╩══════════╩════════════╩══════╩═══════════════╩══════╩═════════╩══════╩══════════╩══════════╩═════════════╝

And here is an EXPLAIN of the sessiondata table:

╔═════════════════╦══════════════════╦══════╦═════╦═════════╦════════════════╗
║      Field      ║       Type       ║ Null ║ Key ║ Default ║     Extra      ║
╠═════════════════╬══════════════════╬══════╬═════╬═════════╬════════════════╣
║ PerfId          ║ int(11) unsigned ║ NO   ║ PRI ║ NULL    ║ auto_increment ║
║ SessionGuid     ║ varchar(45)      ║ NO   ║ MUL ║ NULL    ║                ║
║ BuildId         ║ int(11)          ║ NO   ║ MUL ║ NULL    ║                ║
║ Metric          ║ float            ║ NO   ║     ║ NULL    ║                ║
║ MetricId        ║ int(11) unsigned ║ NO   ║ MUL ║ NULL    ║                ║
║ Location        ║ varchar(45)      ║ NO   ║     ║ NULL    ║                ║
║ RunTypeId       ║ int(11)          ║ NO   ║     ║ NULL    ║                ║
║ MetaContextId   ║ int(11)          ║ NO   ║ MUL ║ -1      ║                ║
║ SecondsFromBoot ║ int(10) unsigned ║ NO   ║     ║ NULL    ║                ║
║ Timestamp       ║ datetime         ║ NO   ║     ║ NULL    ║                ║
║ DeviceId        ║ int(11)          ║ NO   ║ MUL ║ -1      ║                ║
╚═════════════════╩══════════════════╩══════╩═════╩═════════╩════════════════╝

Can you show the table structure for `sessiondata` i.e does it have a column `SessionId`? You can also use `EXPLAIN` inside the procedure to show the query plan. — slaakso, Sep 18 '19 at 18:28
Is the sessiondata a view or why the sp EXPLAIN says the table is `perfinfo`? Also, do you mix character sets (database, table, connection)? — slaakso, Sep 18 '19 at 19:12
I'd suggest you have a look at this article: https://stackoverflow.com/questions/2365132/uuid-performance-in-mysql — ChrisFNZ, Sep 18 '19 at 19:53
@slaakso The table is actually called `perfinfo` sorry for the confusion. As for mixing character sets I have no clue -- how can I check? — Kyle V., Sep 18 '19 at 22:58
@ChrisFNZ Can you explain what part of this article is supposed to answer my question? — Kyle V., Sep 18 '19 at 23:00
It talks about the performance implications of the datatype used to store a uid. In particular the comment in a beige box (with 25 votes) looked like it might be relevant in your case. Theoretically if you're feeding the SP the same SessionGuid = value as the SELECT query, the performance should be identical (well certainly not slower). — ChrisFNZ, Sep 18 '19 at 23:59
@ChrisFNZ Right, I understand the implications of using uids from the article but like you said the performance difference with a literal vs a variable doesn't make sense still. — Kyle V., Sep 19 '19 at 17:07
I know it sounds odd but can you try deleting the SP and then recreating it without the definer clause? i.e. CREATE PROCEDURE... — ChrisFNZ, Sep 19 '19 at 21:06

score 0 · Answer 1 · answered Sep 19 '19 at 08:05

To see if a difference in character set / collation is causing the sp query not to use and index, see if there is difference in the settings:

To see the info on the column:

select CHARACTER_SET_NAME, COLLATION_NAME 
from information_schema.COLUMNS 
where TABLE_SCHEMA='*YOURDATABASENAME*' 
  and TABLE_NAME='sessiondata' and COLUMN_NAME='SessionGuid';

To see the info on the sp's parameter:

select CHARACTER_SET_NAME, COLLATION_NAME 
from information_schema.PARAMETERS 
where SPECIFIC_SCHEMA='*YOURDATABASENAME*' 
  and SPECIFIC_NAME='sp_GetSessionData' and PARAMETER_NAME='SessionId';

And finally the connection details:

show variables where variable_name like 'character_set%' 
  or variable_name like '%collation%';

Ideally the SessionGuid's character set could be a set to 'ascii' so more values would fit in cache.

Stored Procedure slower than equivalent query

1 Answers1