Hi! On 1 Jun 2014, at 17:37, s.ke...@pivotaltechnologies.com.au wrote:
> I have recently moved from an older version of Mysql to MariaDB, I have a few > queries which use internal tmp tables (according to EXPLAIN) and the > performance is almost un-usable on MariaDB for some reason. > Is there anything different in MariaDB that would be causing this issue. The > version I am using is: Ver 15.1 Distrib 10.0.10-MariaDB. > What are your aria engine settings in my.cnf out of curiosity? Try setting join_cache_level = 0 Please also provide @@optimizer_switch output Thanks P/S: what was the older version of mysql that you're referring to? > Here is the explain from the query: > +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra > | > +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using temporary; Using filesort > | > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using where; Using join buffer (flat, > BNL join) | > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using where; Using join buffer > (incremental, BNL join) | > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using where; Using join buffer > (incremental, BNL join) | > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using where; Using join buffer > (incremental, BNL join) | > | 1 | SIMPLE | plots | range | plot_IDX | plot_IDX | 4 | > NULL | 2939 | Using index condition; Using where; Using join buffer > (incremental, BNL join) | > +------+-------------+-------+-------+---------------+----------+---------+------+------+-------------------------------------------------------------------------------+ > > To give you an idea, the exact same query on the old version of mysql took > about 1 second, this version of MariaDB is taking 1min 48secs on the same > hardware. > Any help would be appreciated. > > Thanks Steve. > > -- > You received this message because you are subscribed to the Google Groups > "MariaDB-Manager" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to mariadbmanager+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles _______________________________________________ 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
