maria-discuss team mailing list archive

[Colin Charles <colin@xxxxxxxxxxx>]

Hi!

On 1 Jun 2014, at 17:37, s.kelly@xxxxxxxxxxxxxxxxxxxxxxxxxx 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+unsubscribe@xxxxxxxxxxxxxxxx.
> 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