Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-756

LP:797687 - Inconsistent costs for group by queries with and without index access

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 5.5.36, 10.0.9
        • Fix Version/s: 10.0, 5.5
        • Component/s: None
        • Labels:

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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                timour Timour Katchaounov added a comment - - edited

                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  |
+-----------------------+-------+
                Show
                timour Timour Katchaounov added a comment - - edited 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 | +-----------------------+-------+
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

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

                Show
                elenst Elena Stepanova added a comment - Re: Inconsistent costs for group by queries with and without index access Also filed in JIRA as MDEV-197
                Hide
                Permalink
                timour Timour Katchaounov added a comment - - edited

                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  |
+--------------------------+-------+
                Show
                timour Timour Katchaounov added a comment - - edited 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 | +--------------------------+-------+
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 797687

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 797687

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    timour Timour Katchaounov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated: