I'm trying to select every n-th row from mysql, I read this answer.
There is a table sys_request_log:
CREATE TABLE `sys_request_log`
(
`id` bigint(20) NOT NULL,
`user_id` bigint(20) DEFAULT NULL,
`ip` varchar(50) DEFAULT NULL,
`data` mediumtext,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
It contains 11837 rows.
I try to select every 5-th row from table, first I try to execute:
SELECT
*
FROM
(SELECT @ROW := @ROW + 1 AS rownum, log.* FROM ( SELECT @ROW := 0 ) r, sys_request_log log ) ranked
WHERE
rownum % 5 = 1
The result is:
rownum id user_id create_time
-------------------------------------------------------------------
1 1271446699071639552 1 2020-06-12 22:18:10
6 1271446948980854784 1 2020-06-12 22:19:10
11 1271447016878247936 1269884071484461056 2020-06-12 22:19:26
It costs 1.001s time
I found there is a unrelated column rownum. So I modify the SQL like this:
SELECT
log.*
FROM
(SELECT @ROW := @ROW + 1 AS rownum FROM (SELECT @ROW := 0) t) r,
sys_request_log log
WHERE
rownum % 5 = 1
Now the result is clean (no rownum), but It costs 2.516s time!
Why?
Mysql version: 5.7.26-log
In the first case, the row number values are selected during the selection from the table(sys_request_log), but for the second case there occurs a cartesian product among subquery r and the selection from the table because of the CROSS JOIN occurence for each individual rownum versus each individual row value of the table.
If I understand correctly, you can do what you want by moving the variable assignment to the where clause:
select srl.*
from sys_request_log srl cross join
(select @rn := 0) params
where (@rn := (@rn + 1)) % 5 = 1;
Note this happens to work in this case, because the query needs to do a full table scan and run the WHERE clause on each row. It might not work if the query has a JOIN, GROUP BY or ORDR BY.
The use of variables in this way is deprecated in MySQL now. You should upgrade and learn about window functions.
Your second query has different result from the first one and returns all rows, so take much more time from the first one.
To remove rownum from first query, Just name fields in SELECT clause.
try this:
SELECT
ranked.id, ranked.user_id ,ranked.create_time
FROM
( SELECT @ROW := @ROW + 1 AS rownum, log.* FROM ( SELECT @ROW := 0 ) r, sys_request_log log ) ranked
WHERE
rownum % 5 = 1