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: | |
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 |
[9 Feb 2007 15:21]
Mark Kubacki
[9 Feb 2007 18:16]
Mark Kubacki
Changing severity to serious as keys/indices are essential in DBS and stored procedures are extensively used in certain environments.
[4 Mar 2007 19:13]
Valeriy Kravchuk
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.
[13 Apr 2007 13:12]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.17, that is already released officially. Inform about the results.
[13 Apr 2007 20:07]
Mark Kubacki
Unfortunately, the bug still exists in MySQL 5.1.17-beta. Did you manage to reproduce that issue?
[18 May 2007 11:23]
Mark Kubacki
No luck with 5.1.18-beta. Direct query uses keys, the same query in stored procedure ignores them.
[2 Sep 2007 19:09]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.21-beta, and inform about the results.
[2 Oct 2007 23:00]
Bugs System
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".
[12 Nov 2007 21:32]
Mark Kubacki
Still no luck with 5.1.22_rc - keys are ingored, querry takes within a procedure 36 seconds and outside 0.12s.
[12 Nov 2007 22:30]
Mark Kubacki
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.)
[25 Nov 2007 16:17]
Valeriy Kravchuk
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.
[26 Dec 2007 0:00]
Bugs System
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".
[29 Dec 2007 22:24]
Mark Kubacki
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.
[30 Dec 2007 20:13]
Valeriy Kravchuk
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.
[31 Jan 2008 0:00]
Bugs System
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".