Re: Query with variables very slow

[Gianni Moretti]

First of all, thanks for the reply!

I'm sure that the problem isn't related to cursors: I just described my procedure just to explain why I need to use query with variables.

I'm not sure that the problem can be in the table because without the variable the query is very fast and I'm expecting that when I use a variable there is just a replace before execute the query.

To simlify:

CREATE PROCEDURE `new_routine`()
BEGIN
DECLARE myname VARCHAR(50);
SET myname = 'MYVALUE';
SELECT * FROM mytable WHERE key=myname;
END

is about 20 time more slower than:

CREATE PROCEDURE `new_routine`()
BEGIN
SELECT * FROM mytable WHERE key='MYVALUE';
END

'mytable' contains 800,000 row, 'key' is one of the primary keys, there is an index for 'key' and the select retrive about 20 records.

So my question/problem is why the same query, using a variable become very slow.

There are the buffer variables:
'bulk_insert_buffer_size', '8388608'
'innodb_buffer_pool_size', '8388608'
'innodb_log_buffer_size', '1048576'
'join_buffer_size', '131072'
'key_buffer_size', '16777216'
'myisam_sort_buffer_size', '8388608'
'net_buffer_length', '16384'
'preload_buffer_size', '32768'
'read_buffer_size', '131072'
'read_rnd_buffer_size', '262144'
'sort_buffer_size', '2097144'
'sql_buffer_result', 'OFF'

Someone have any ideas?

Thanks

Gianni