maria-discuss team mailing list archive

[Alessandro Ren <dirty.ren@xxxxxxxxx>]

   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
>