Inconsistent costs for group by queries with and without index access

Bug #797687 reported by Timour Katchaounov on 2011-06-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
Low
Timour Katchaounov

Bug Description

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) wrote :
Download full text (3.3 KiB)

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 ...

Read more...

Changed in maria:
milestone: none → 5.3
status: New → Confirmed
importance: Undecided → Medium
assignee: nobody → Timour Katchaounov (timour)
Changed in maria:
importance: Medium → Low
Michael Widenius (monty) on 2012-03-21
Changed in maria:
milestone: 5.3 → 5.5
Elena Stepanova (elenst) wrote :

Also filed in JIRA as MDEV-197

Elena Stepanova (elenst) on 2012-03-29
tags: added: optimizer
Timour Katchaounov (timour) wrote :

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 |
+--------------------------+-------+

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers