Re: Query with GROUP BY (Using index for group-by -> Using index)

[mnf]

If somebody is interested, I rewrote above query as LOOP to avoid dependent subquery. Now it works much faster than other variants:

mysql> CALL spOK(10000000000);
...
Query OK, 0 rows affected, 1 warning (0.19 sec)

BTW Shlomo Swidler in "Liberating Dependent Subqueries" described a more general way to do this. It is great, but perhaps anybody knows how to rewrite this w/out loops?

===

DROP PROCEDURE IF EXISTS spOk;
DELIMITER $$
CREATE PROCEDURE spOk(IN i_ INT UNSIGNED)
BEGIN
DECLARE v INT UNSIGNED;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT MAX(i) FROM shn WHERE i <= i_ GROUP BY n;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET @q = "SELECT i, n, t, v FROM shn WHERE i IN (";
SET @sp = "";
OPEN cur;
l: LOOP
FETCH cur INTO v;
IF done THEN
LEAVE l;
END IF;
SET @q = CONCAT(@q, @sp, v);
SET @sp = ',';
END LOOP;
CLOSE cur;
SET @q = CONCAT(@q, '', ')');
PREPARE stmt from @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;