Inconsistent costs for group by queries with and without index access

[Timour Katchaounov]

The following example over the world database from the 5.3/mysql-test/include directory
shows that a query that uses an index access to compute GROUP BY has the same cost
as the same query without index access (using temp table). At the same time the two
different query plans indeed show different number of Handler_read_* calls.

Test script:

create database world;
use world;

source include/world_schema.inc
source include/world.inc

create index CityNmae on City(Name);

explain
SELECT name FROM City GROUP BY name HAVING Count(*) > 2;
flush status;
SELECT name FROM City GROUP BY name HAVING Count(*) > 2;
show status like 'last_query_cost';
show status like 'Handler_read%';

explain
SELECT name FROM City ignore index (cityname) GROUP BY name HAVING Count(*) > 2;
flush status;
SELECT name FROM City ignore index (cityname) GROUP BY name HAVING Count(*) > 2;
show status like 'last_query_cost';
show status like 'Handler_read%';

[Timour Katchaounov (timour)]

The output of the script above (edited for brevity):

MariaDB [world]> explain
    -> SELECT name FROM City GROUP BY name HAVING Count(*) > 2;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | City | index | NULL | CityName | 35 | NULL | 4080 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [world]> flush status;
Query OK, 0 rows affected (0.00 sec)

MariaDB [world]> SELECT name FROM City GROUP BY name HAVING Count(*) > 2;
+--------------+
| name |
+--------------+
| Cambridge |
..............................
| Victoria |
+--------------+
13 rows in set (0.02 sec)

MariaDB [world]> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 4944.815406 |
+-----------------+-------------+
1 row in set (0.01 sec)

MariaDB [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 4080 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)

MariaDB [world]>
MariaDB [world]> explain
    -> SELECT name FROM City ignore index (cityname) GROUP BY name HAVING Count(*) > 2;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4080 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

MariaDB [world]> flush status;
Query OK, 0 rows affected (0.00 sec)

MariaDB [world]> SELECT name FROM City ignore index (cityname) GROUP BY name HAVING Count(*) > 2;
+--------------+
| name |
+--------------+
| Cambridge |
.........................
| Victoria |
+--------------+
13 rows in set (0.02 sec)

MariaDB [world]> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 4944.815406 |
+-----------------+-------------+
1 row in set (0.00 sec)

MariaDB [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 4080 |
| Handler_read_next ...

[Elena Stepanova (elenst)]

Also filed in JIRA as MDEV-197

[Timour Katchaounov (timour)]

In MariaDB 5.5 the bug still exists with the following query statistics:

- If executed with index:

MariaDB [test]> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 5117.799000 |
+-----------------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> show status like 'Handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 4079 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
+--------------------------+-------+

- Without index:

MariaDB [test]> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 5117.799000 |
+-----------------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> show status like 'Handler_read%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 4079 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 13 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 8079 |
+--------------------------+-------+