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...@roze.lv> 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
