MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query with GROUP BY (Using index for group-by -> Using index)
Posted by: mnf
Date: December 06, 2006 02:03AM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query with GROUP BY (Using index for group-by -> Using index)
3321
December 06, 2006 02:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.