MySQL Bugs: #26224: optimizer ignores keys in stored procedures

Bug #26224	optimizer ignores keys in stored procedures
Submitted:	9 Feb 2007 15:21	Modified:	30 Jan 2008 20:13
Reporter:	Mark Kubacki	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.22_rc	OS:	Linux (GNU/Linux)
Assigned to:		CPU Architecture:	Any
Tags:	charset, key, optimizier, primary, stored procedure

Description:
A query outside a stored procedure has other optimizations set than inside a stored procedure. In the latter case some keys, especially a primary key, are ignored.

Symptoms of bug #25971 seem similar although presumtions differ quite a lot.

How to repeat:
CREATE TABLE `identity` (
	`ID` INT UNSIGNED NOT NULL ,
	`name` VARCHAR( 200 ) NOT NULL ,
	`class` CHAR( 1 ) NULL ,
	`type` VARCHAR( 10 ) NULL ,
	`country_code` CHAR( 2 ) NOT NULL ,
	`mod_date` DATE NULL ,
	PRIMARY KEY (`country_code`, `ID`),
	INDEX (`name`(16)),
	INDEX (`type`)
) ENGINE = MYISAM;

-- Populate the table with millions of entries.

-- stored procedure for comparison
DELIMITER $$

DROP PROCEDURE IF EXISTS SEARCH_FOR_IDENTITY $$
CREATE PROCEDURE SEARCH_FOR_IDENTITY (p_country_code CHAR(2), p_name VARCHAR(200))
	DETERMINISTIC READS SQL DATA
BEGIN
	EXPLAIN SELECT country_code, ID, name, class, type
	FROM identity
	WHERE country_code=p_country_code
		AND name=p_name AND class='P'
	ORDER BY ID DESC, LENGTH(type) DESC;
END $$

DELIMITER ;

--------------------------------------------------------------
EXPLAIN SELECT country_code, ID, name, class, type
FROM identity
WHERE country_code='DE'
	AND name='Babaev, Igor' AND class='P'
ORDER BY ID DESC, LENGTH(type) DESC\G
-- yields in
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: identity
         type: ref
possible_keys: PRIMARY,name
          key: name
      key_len: 18
          ref: const
         rows: 1
        Extra: Using where; Using filesort

--------------------------------------------------------------
CALL SEARCH_FOR_IDENTITY('DE', 'Babaev, Igor');
-- yields in:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: identity
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 380313720
        Extra: Using where; Using filesort

Suggested fix:
Even the FORCE INDEX clause seems to be ignored within stored procedure.

Changing severity to serious as keys/indices are essential in DBS and stored procedures are extensively used in certain environments.

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.16, and inform about the results. In case of the same problem, please, send/upload dump of smallest data set that demonstrates the behaviour described.

I can not repeat this with dummy/random data neither on 5.1.17-BK nor on 5.0.38-BK, hence the requests. PRIMARY key is used both in SP and in simple SELECT you sent.

Please, try to repeat with a newer version, 5.1.17, that is already released officially. Inform about the results.

Unfortunately, the bug still exists in MySQL 5.1.17-beta.

Did you manage to reproduce that issue?

No luck with 5.1.18-beta.

Direct query uses keys, the same query in stored procedure ignores them.

Please, try to repeat with a newer version, 5.1.21-beta, and inform about the results.

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".

Still no luck with 5.1.22_rc - keys are ingored, querry takes within a procedure 36 seconds and outside 0.12s.

After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!

The question I cannot answer is: Why does the optimizer treat charset conversions an other way within and outside stored procedures? (Indeed, I might be wrong asking this.)

Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html:

"For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation that are in effect at the time the routine is created are used. (These are given by the values of the character_set_database and collation_database system variables.)"

I think, the quote above explains the difference.

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".

Yes, that's true. (I've read this part of the documentation.)

But, there is the difference in how the optimizer runs the querries. 
Inside a SP, every of the million rows is converted and keys get ignored. 
Outside keys are utilized and the single *input* is converted!
The latter seems to me being the right approach to be chosen by the optimizer.

Still an open bug, sorry.

For stored procedure parameter database character set is used by default (see that manual quote), not connection character set. This should explain the difference of execution in SP and outside SP, and the fact that if you set character set explicitely (to UTF-8?) indexes are used.

Please, check if above is the case.

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".