Query in stored procedure does not use primary index and very slow

Question

I'm attempting to use a stored procedure.

DELIMITER //
CREATE PROCEDURE cuiTitle(IN cui_in CHAR(8))
BEGIN
    EXPLAIN SELECT str FROM MRCONSO
        WHERE cui = cui_in
        AND ts = 'P' 
        AND stt = 'PF' 
        AND ispref = 'Y' 
        LIMIT 1;
END //
CALL cuiTitle('C0269037');

The query is very slow. it does a fully scan instead of using the primary index.

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  MRCONSO NULL    ALL NULL    NULL    NULL    NULL    5814290 0.10    Using where

If querying directly or using hardcoded value instead of parameter, the query is very fast and uses the primary key.

DELIMITER //
CREATE PROCEDURE cuiTitle(IN cui_in CHAR(8))
BEGIN
    EXPLAIN SELECT str FROM MRCONSO
        WHERE cui = 'C0269037'
        AND ts = 'P' 
        AND stt = 'PF' 
        AND ispref = 'Y' 
        LIMIT 1;
END //
CALL cuiTitle('C0269037');

its "explain":

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  MRCONSO NULL    ref PRIMARY PRIMARY 24  const   10  0.50    Using where

Why is it? how can I make the stored procedure use the primary key? I've tried https://stackoverflow.com/a/21687188/218677 recommendation but with no success..

UPDATE: I don't know if the makes a difference, but it primary key is: PRIMARY KEY (CUI,AUI),

score 0 · Answer 1 · answered May 12 '20 at 19:53

There are possibilitys to hint mysql to use an index as far as i can see without seeing your table, that you don't have a combined index for (cui , ts, stt,ispref), which you could also use .

  SELECT str FROM MRCONSO
  USE INDEX (PRIMARY)
        WHERE cui = cui_in
        AND ts = 'P' 
        AND stt = 'PF' 
        AND ispref = 'Y' 
        LIMIT 1;

And

  SELECT str FROM MRCONSO
  FORCE INDEX (PRIMARY)
        WHERE cui = cui_in
        AND ts = 'P' 
        AND stt = 'PF' 
        AND ispref = 'Y' 
        LIMIT 1;

Also use the combined index mentioned in the answer. – nbk May 12 '20 at 20:02 — nbk, May 12 '20 at 20:02

score 0 · Accepted Answer · answered May 12 '20 at 20:02

turns out https://stackoverflow.com/a/21687188/218677 was correct. I've got the wrong char set

DELIMITER //
CREATE PROCEDURE cuiTitle(IN cui_in CHAR(8))
BEGIN
    EXPLAIN SELECT str FROM MRCONSO
 FORCE INDEX (PRIMARY)
        WHERE cui = convert(cui_in using utf8)
        AND ts = 'P' 
        AND stt = 'PF' 
        AND ispref = 'Y' 
        LIMIT 1;
END //

Works

Query in stored procedure does not use primary index and very slow

2 Answers2