Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
COUNT(*) vs COUNT(1) in MySQL
-- 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
You can’t perform that action at this time.