maria-discuss team mailing list archive

[Alessandro Ren <dirty.ren@xxxxxxxxx>]

  Jocelyn,

the bug described there seems similar to my problem and it works on MariaDB
10.0.23.

[]s.


On Mon, Jul 24, 2017 at 1:31 PM, jocelyn fournier <
jocelyn.fournier@xxxxxxxxxxx> wrote:

> Hi Alessandro!
>
>
> 10.0.31 should be affected by https://jira.percona.com/browse/TDB-35 , do
> you think it could be related to your issue?
>
>
> HTH,
>
> Jocelyn Fournier
> Founder
> M : +33 6 51 21 54 10 <+33%206%2051%2021%2054%2010>https://www.softizy.com
> Softizy - At your side to Optimize your PHP / MySQL applications
>
> Le 24/07/2017 à 17:41, Alessandro Ren a écrit :
>
>
>  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
>>>
>>
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>
>