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....@gmail.com> 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...@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
