mysql 5.1: how can i use benchmark() command to test a call to a stored procedure ?

Question

Hello

I'm trying to benchmark a stored procedure.

  select benchmark(100000000,(select 1));

this benchmark works

but the following benchmark doesn't:

do benchmark(1000,(call test_login_user('a')));

it produces the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call xpofb_login_user('a')))' at line 1

any ideas how to resolve the issue ?

Answer 1

You can't

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

Only scalar expressions can be used. Although the expression can be a subquery, it must return a single column and at most a single row. For example, BENCHMARK(10, (SELECT * FROM t)) will fail if the table t has more than one column or more than one row.

Answer 2

You can't do this with benchmark(), but you could create a stored procedure to do it.

Here's an example:

delimiter $$

create procedure benchmark_test_login_user (p_username varchar(100), 
  p_count int unsigned)
begin
  declare v_iter int unsigned;
  set v_iter = 0;
  while v_iter < p_count
  do
    call test_login_user(p_username);
    set v_iter = v_iter + 1;
  end while;
end $$

delimiter ;

call benchmark_test_login_user('a',1000);