In this blog post, I am going to show why we have to be careful
with stored functions in select list, as a single
query can cause thousands of queries in the background if we
aren’t cautious.
For this example, I am only going to use the SLEEP
function to demonstrate the issue, but you could use any other
stored functions.
Here is the test schema and data:
DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT NOT NULL PRIMARY KEY auto_increment, gcol INT NOT NULL, fcol INT NOT NULL, INDEX (gcol) ); INSERT INTO t1 VALUES (NULL,1,1),(NULL,2,1),(NULL,3,1),(NULL,4,2),(NULL,5,1);
And the first query:
mysql [localhost] {msandbox} (test) > SELECT gcol, SLEEP(1) FROM t1 WHERE 1 GROUP BY gcol LIMIT 1;
+------+----------+
| gcol | SLEEP(1) |
+------+----------+
| 1 | 0 |
+------+----------+
1 row in set (1.00 sec)
The query takes one second, which means the …
[Read more]
