Created
September 19, 2019 08:41
-
-
Save lukaseder/c73fd305ae52071b35af6fd3e4c30e4e to your computer and use it in GitHub Desktop.
COUNT(*) vs COUNT(1) in MySQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Copyright Data Geekery GmbH | |
-- | |
-- Licensed under the Apache License, Version 2.0 (the "License"); | |
-- you may not use this file except in compliance with the License. | |
-- You may obtain a copy of the License at | |
-- | |
-- http://www.apache.org/licenses/LICENSE-2.0 | |
-- | |
-- Unless required by applicable law or agreed to in writing, software | |
-- distributed under the License is distributed on an "AS IS" BASIS, | |
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
-- See the License for the specific language governing permissions and | |
-- limitations under the License. | |
-- | |
CREATE TABLE t (i INT); | |
INSERT INTO t | |
WITH RECURSIVE d (i) AS ( | |
SELECT 1 | |
UNION ALL | |
SELECT i + 1 | |
FROM d | |
WHERE i < 1000 | |
) | |
SELECT row_number() OVER (ORDER BY (SELECT 1)) FROM d AS d1, d AS d2; | |
CREATE TABLE IF NOT EXISTS print_relative (run INT, stmt INT, elapsed BIGINT); | |
delimiter // | |
CREATE PROCEDURE benchmark () | |
BEGIN | |
DECLARE done INT DEFAULT FALSE; | |
DECLARE v_ts BIGINT; | |
DECLARE v_repeat INT DEFAULT 100; | |
DECLARE r, c INT; | |
DECLARE a INT; | |
DECLARE cur1 CURSOR FOR | |
SELECT COUNT(*) FROM t; | |
DECLARE cur2 CURSOR FOR | |
SELECT COUNT(1) FROM t; | |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; | |
SET r = 0; | |
REPEAT | |
SET v_ts = ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000); | |
SET c = 0; | |
REPEAT | |
OPEN cur1; | |
read_loop: LOOP | |
FETCH cur1 INTO a; | |
IF done THEN | |
LEAVE read_loop; | |
END IF; | |
END LOOP; | |
CLOSE cur1; | |
SET c = c + 1; | |
UNTIL c >= v_repeat END REPEAT; | |
INSERT INTO print_relative VALUES (r, 1, ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) - v_ts); | |
SET v_ts = ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000); | |
SET c = 0; | |
REPEAT | |
OPEN cur2; | |
read_loop: LOOP | |
FETCH cur2 INTO a; | |
IF done THEN | |
LEAVE read_loop; | |
END IF; | |
END LOOP; | |
CLOSE cur2; | |
SET c = c + 1; | |
UNTIL c >= v_repeat END REPEAT; | |
INSERT INTO print_relative VALUES (r, 2, ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000) - v_ts); | |
SET r = r + 1; | |
UNTIL r >= 5 END REPEAT; | |
END// | |
delimiter ; | |
CALL benchmark(); | |
SELECT | |
run, | |
stmt, | |
CAST(elapsed / MIN(elapsed) OVER() AS DECIMAL(20, 4)) ratio, | |
' ' `Copyright Data Geekery GmbH` | |
FROM print_relative | |
UNION ALL | |
SELECT null, null, null, null | |
UNION ALL | |
SELECT null, null, null, 'Copyright Data Geekery GmbH' | |
UNION ALL | |
SELECT null, null, null, 'https://www.jooq.org/benchmark'; | |
DROP PROCEDURE benchmark; | |
DROP TABLE print_relative; | |
DROP TABLE t; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment