Hi,
I am new here,
Not sure if this is the appropriate place. If not, any pointers are much
appreciated.
Not sure if this is a bug that I should report it. Any input on that matter
will be much appreciated also.
Platform: Debian unstable/SID, MariaDB: 10.5.12-MariaDB-1 SSD disks joined in a
LVM setup.
I was investigating a bug where GROUP BY is not working in a huge table of mine
when I noticed the following discrepancy.
I managed to create a trivial reproducer with a 10 rows table.
If I don't specify LIMIT the plan goes to filesort.
If I specify LIMIT <= 9 the plan goes to utilize the index
If I specify LIMIT >= 10 (table rows) the plan foes to filesort.
Is this behavior expected? Do you think I should report it?
Thanks in advance.
Vassilis Virvilis
# make sure you have no table named t that holds your precious data
#DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT, val INT);
INSERT INTO t SELECT seq, FLOOR( 1 + RAND() *60 ) FROM seq_1_to_10;
ALTER TABLE t ADD INDEX(id);
EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id;
SELECT COUNT(*) FROM t;
EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 9;
EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 10;
EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id LIMIT 11;
Output:
MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id;
1
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
2 | id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
3
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL |
NULL | 10 | Using filesort |
5
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.034 sec)
MariaDB [MEDLINE]> SELECT COUNT(*) FROM t;
1 +----------+
2 | COUNT(*) |
3 +----------+
4 | 10 |
5 +----------+
1 row in set (0.001 sec)
MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id
LIMIT 9;
1
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+
2 | id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
3
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+
4 | 1 | SIMPLE | t | index | NULL | id | 5 |
NULL | 9 | |
5
+------+-------------+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.001 sec)
MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id
LIMIT 10;
1
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
2 | id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
3
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL |
NULL | 10 | Using filesort |
5
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.000 sec)
MariaDB [MEDLINE]> EXPLAIN SELECT id, GROUP_CONCAT(val) vals FROM t GROUP BY id
LIMIT 11;
1
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
2 | id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
3
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
4 | 1 | SIMPLE | t | ALL | NULL | NULL | NULL |
NULL | 10 | Using filesort |
5
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.000 sec)
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
