| Bug #13945 | BENCHMARK() function not fully implemented | ||
|---|---|---|---|
| Submitted: | 12 Oct 2005 3:01 | Modified: | 12 Oct 2005 3:26 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 5.0.13-RC/BK source | OS: | Solaris (solaris 9) |
| Assigned to: | CPU Architecture: | Any | |
[12 Oct 2005 3:01]
[ name withheld ]
[12 Oct 2005 3:26]
MySQL Verification Team
Looks most as a feature request, even the Manual not explains that limitation: http://dev.mysql.com/doc/mysql/en/information-functions.html 12.9.3. Information Functions BENCHMARK(count,expr) The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is from within the mysql client, which reports query execution times: mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye')); +----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (4.74 sec) The time reported is elapsed time on the client end, not CPU time on the server end. It is advisable to execute BENCHMARK() several times, and to interpret the result with regard to how heavily loaded the server machine is. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 5.0.15-rc-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table T (a int not null auto_increment primary key, b char(20)); Query OK, 0 rows affected (0.06 sec) mysql> insert into T set b='abcde'; Query OK, 1 row affected (0.00 sec) mysql> insert into T set b='abcde'; Query OK, 1 row affected (0.00 sec) mysql> select BENCHMARK(5000000,(select a from T where a=1)); +------------------------------------------------+ | BENCHMARK(5000000,(select a from T where a=1)) | +------------------------------------------------+ | 0 | +------------------------------------------------+ 1 row in set (0.85 sec) mysql> select BENCHMARK(5000000,(select a,b from T where a=1)); ERROR 1241 (21000): Operand should contain 1 column(s)
[12 Oct 2005 3:33]
Paul DuBois
There is little reason that the manual _should_ mention this limitation in the BENCHMARK section, because it's not specific to BENCHMARK(): To use a subquery as an argument to _any_ function, it has to be a scalar subquery. This is just a general property of where subqueries can be used, not a property of BENCHMARK() itself.
[12 Oct 2005 13:44]
Sergei Golubchik
I don't see how this "limitaton" can be lifted...
[7 Dec 2007 11:10]
Davide D'Alessandro
The benchmark is not fixed in 5.1.22 release. Also the benchmark does not permit the use of a select statement the returns more than one result: mysql> select benchmark(10000,(select countrycode from city where countrycode='u sa')); ERROR 1242 (21000): Subquery returns more than 1 row because of a subquery error. Better use mysqlslap to perform this type of testing. Bye
[4 Oct 2008 9:36]
Konstantin Osipov
Workaround: wrap the functionality being tested into a stored function. You will have to, however, intercept the result set into a tmp table or a cursor, which may skew benchmark results. On the other hand, if you really would like to time the cost of a select, you should also account for the cost of sending the data over network, which isn't done by benchmark().
[10 Oct 2019 22:00]
Roy Lyseng
It is possible to workaround this. You can use a row subquery contained in a comparison operator, which returns a single value. Example: select BENCHMARK(5000000,(select a,a from T where a=1)=(1,1)); The drawback is that you will also benchmark the comparison.
