A post by KimSeong Loh (http://forums.mysql.com/read.php?115,128977,130042#msg-130042) called my attention to the GROUP_CONCAT function. We can rewrite constify to use GROUP_CONCAT instead of the CURSOR for additional speed improvement.
GROUP_CONCAT does almost exactly what constify does. From the MySQL manual:
This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
In my example from my orignal post, we could use this function as follows:
SELECT GROUP_CONCAT(tag_id) FROM tags WHERE LOCATE('bunk', teg_text)>0
This will provide a string listing all the tag_ids of offending tags.
Such a string is exactly what we sought when we wrote constify in the first place. Remember, we're looking for a better way to do the following:
DELETE FROM book_tags WHERE tag_id IN (SELECT tag_id FROM tags WHERE LOCATE('bunk', tag_text)>0))
We could use GROUP_CONCAT to simplify the query, as follows:
mysql> SELECT GROUP_CONCAT(tag_id) INTO @result FROM tags WHERE LOCATE('bunk', tag_text)>0;
mysql> SET @query=CONCAT("DELETE FROM book_tags WHERE tag_id IN (", @result, ")");
mysql> PREPARE st FROM @query;
mysql> EXECUTE st;
mysql> DEALLOCATE PREPARE st;
This would work, and it would be faster than the original version of constify.
But in the implementation of liberateSubquery (see parent post) we can't use GROUP_CONCAT. The issue is that GROUP_CONCAT requires that you know the column name that you wish to CONCAT together. As you see in the above example, we knew that we wanted to create a string list of the tag_ids. But in liberateSubquery we do not know the name of the column we want to CONCAT. We don't know what subquery the caller will provide! So we can't know what the column name is. Thus, we can't directly use GROUP_CONCAT inside liberateSubquery - we must still use constify from liberateSubquery.
But we can improve constify by using GROUP_CONCAT! Here it is:
CREATE PROCEDURE `constify`(IN query_ TEXT, OUT constStr_ TEXT)
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Turns the results of the given query into a comma-separated str.'
BEGIN
-- The length of each individual element is limited to 50 chars.
-- Change the CREATE TEMPORARY TABLE constifyResults statement below as needed to change this limitation.
-- Note that the length of the result is limited by the system variable group_concat_max_len, which defaults to 1M.
-- The system variable max_allowed_packet also limits the size of the result. This is 1M by default.
SET @query = query_;
SET @insertStmt = CONCAT("INSERT INTO constifyResults ", @query);
DROP TABLE IF EXISTS constifyResults;
-- You might consider resizing the item column smaller to save space in memory
CREATE TEMPORARY TABLE constifyResults (item VARCHAR(50)) ENGINE=MEMORY;
-- If you need unlimited length for an element then you can define the table as follows:
-- CREATE TEMPORARY TABLE constifyResults (item TEXT);
-- but then the table is not in memory. This stored procedure will run slightly slower as a result.
PREPARE stmt FROM @insertStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT GROUP_CONCAT(item) INTO @constifyResult FROM constifyResults;
SET constStr_ = @constifyResult;
END
In order to ensure that we know the column name which we want to GROUP_CONCAT, we separate the (temporary) table creation into two statements. The first statement creates the table (CREATE TEMPORARY TABLE...) with the column name 'item'. The second statement, the prepared statement, fills the temporary table with the results defined by the query_ parameter. Then, the GROUP_CONCAT statement uses the 'item' column as required.
There may be even more possible improvements to liberateSubquery and/or constify - I'd love to hear about them.