[MDEV-756] LP:797687 - Inconsistent costs for group by queries with and without index access Created: 2011-06-15  Updated: 2022-09-08

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.36, 10.0.9
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Timour Katchaounov (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug797687.xml    

 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%';



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-06-15 ]

Re: Inconsistent costs for group by queries with and without index access
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]> 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     | 0     |
 
| Handler_read_prev     | 0     |
 
| Handler_read_rnd      | 13    |
 
| Handler_read_rnd_next | 8084  |
 
+-----------------------+-------+

Comment by Elena Stepanova [ 2012-03-21 ]

Re: Inconsistent costs for group by queries with and without index access
Also filed in JIRA as MDEV-197

Comment by Rasmus Johansson (Inactive) [ 2012-04-27 ]

Launchpad bug id: 797687

Comment by Timour Katchaounov (Inactive) [ 2012-04-27 ]

Re: Inconsistent costs for group by queries with and without index access
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  |
 
+--------------------------+-------+

Generated at Thu Feb 08 06:31:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.