maria-discuss team mailing list archive

[Alessandro Ren <dirty.ren@xxxxxxxxx>]

 Even if a change the order by and the query to include all 3 fields on the
index, it still selects the PRIMARY key to query the table.

   Do you think this is a bug?

   Tks.


On Mon, Jul 24, 2017 at 11:34 AM, Alessandro Ren <dirty.ren@xxxxxxxxx>
wrote:

>
>    Once cached, the query returns in 0s.
>
>    The force index solved the problem:
>
>     MariaDB 10.0.31
>      Force index:13 rows in set (2.31 sec)
>      No force: 13 rows in set (12.97 sec)
>
>    MariDB 10.0.25:
>      Force index: 13 rows in set (1.46 sec)
>      No force: 13 rows in set (1.70 sec)
>
>
>
> Explains per version follows:
>
> 10.0.25
> MariaDB [opperf]> show variables like '%version%';
> +-------------------------+------------------+
> | Variable_name           | Value            |
> +-------------------------+------------------+
> | innodb_version          | 5.6.29-76.2      |
> | protocol_version        | 10               |
> | slave_type_conversions  |                  |
> | tokudb_version          | 5.6.26-74.0      |
> | version                 | 10.0.25-MariaDB  |
> | version_comment         | MariaDB Server   |
> | version_compile_machine | x86_64           |
> | version_compile_os      | Linux            |
> | version_malloc_library  | bundled jemalloc |
> +-------------------------+------------------+
> 9 rows in set (0.09 sec)
>
> +------+-------------+------------------+-------+-----------
> -----------------+--------------------+---------+------+----
> ---+----------------------------------------------+
> | id   | select_type | table            | type  | possible_keys
>    | key                | key_len | ref  | rows  | Extra
>                      |
> +------+-------------+------------------+-------+-----------
> -----------------+--------------------+---------+------+----
> ---+----------------------------------------------+
> |    1 | SIMPLE      | service_perf_651 | range |
> PRIMARY,service_perf_1_idx | service_perf_1_idx | 16      | NULL | 17880 |
> Using where; Using temporary; Using filesort |
> +------+-------------+------------------+-------+-----------
> -----------------+--------------------+---------+------+----
> ---+----------------------------------------------+
>
>
> 10.0.31
>
> MariaDB [opperf]> show variables like '%version%';
> +-------------------------+------------------+
> | Variable_name           | Value            |
> +-------------------------+------------------+
> | innodb_version          | 5.6.36-82.0      |
> | protocol_version        | 10               |
> | slave_type_conversions  |                  |
> | tokudb_version          | 5.6.36-82.0      |
> | version                 | 10.0.31-MariaDB  |
> | version_comment         | MariaDB Server   |
> | version_compile_machine | x86_64           |
> | version_compile_os      | Linux            |
> | version_malloc_library  | bundled jemalloc |
> +-------------------------+------------------+
> 9 rows in set (0.11 sec)
>
>
> +------+-------------+------------------+-------+-----------
> -----------------+---------+---------+------+------+--------
> --------------------------------------+
> | id   | select_type | table            | type  | possible_keys
>    | key     | key_len | ref  | rows | Extra
>          |
> +------+-------------+------------------+-------+-----------
> -----------------+---------+---------+------+------+--------
> --------------------------------------+
> |    1 | SIMPLE      | service_perf_651 | range |
> PRIMARY,service_perf_1_idx | PRIMARY | 16      | NULL |    1 | Using where;
> Using temporary; Using filesort |
> +------+-------------+------------------+-------+-----------
> -----------------+---------+---------+------+------+--------
> --------------------------------------+
>
>
> tks.
>
>
>
>
>
> On Mon, Jul 24, 2017 at 11:10 AM, Reinis Rozitis <r@xxxxxxx> wrote:
>
>> MariaDB 10.0.25 - 13 rows in set (1.67 sec)
>>> MariaDB 10.0.31 - 13 rows in set (29.06 sec)
>>>
>>> +------+-------------+------------------+-------+-----------
>>> -----------------+---------+---------+------+------+--------
>>> --------------------------------------+
>>> | id   | select_type | table            | type  | possible_keys | key
>>>  | key_len | ref  | rows | Extra |
>>> +------+-------------+------------------+-------+-----------
>>> -----------------+---------+---------+------+------+--------
>>> --------------------------------------+
>>> |    1 | SIMPLE      | service_perf_651 | range |
>>> PRIMARY,service_perf_1_idx | PRIMARY | 16      | NULL |    1 | Using where;
>>> Using temporary; Using filesort |
>>> +------+-------------+------------------+-------+-----------
>>> -----------------+---------+---------+------+------+--------
>>> --------------------------------------+
>>>
>>
>>
>> This is typical when the MySQL query optimiser decides that it will be
>> faster to use an index for sorting rather than selecting.
>> Sometimes the query plans change because of versions or table/index
>> statistics.
>>
>>
>> For testing purposes you can try to drop the "order by entry_time" part
>> or add FORCE INDEX:
>>
>> SELECT ... from service_perf_651  FORCE INDEX(service_perf_1_idx) WHERE
>> ...
>>
>> rr
>>
>
>