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.fourn...@softizy.com> 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....@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 > > >
_______________________________________________ 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
