Percona 5.7 Slow on many Joins

Question

We recently upgraded our percona 5.5 sql server to percona 5.7. Works out fine so far. unfortunately we have a huge query, which is extremly slower under 5.7. Under 5.5. it takes less than a second, even with sql_no_cache. With Percona 5.7. it takes up to 1 min to execute this query. strange thing is, as more combined indicies we use, the slower it becomes. removing all combined indicies leads to an execution time of 30 secondes. forcing sql_straight_join makes the query run in less than a second.

so here is the query:

SELECT t0_.tree_id AS tree_id0, t1_.treetype_name AS treetype_name1, c2_.contentelement_id AS contentelement_id2, t0_.tree_name AS tree_name3, (CASE WHEN t3_.treetype_name <> 'global' THEN t4_.tree_name ELSE t0_.tree_name END) AS sclr4, p5_.picture_id AS picture_id5, t6_.tree_misc_value_text AS tree_misc_value_text6, (CASE WHEN t3_.treetype_name <> 'global' THEN t7_.tree_misc_value_text ELSE t6_.tree_misc_value_text END) AS sclr7, w8_.widgetgeneral_slug AS widgetgeneral_slug8, (CASE WHEN t3_.treetype_name <> 'global' THEN w9_.widgetgeneral_slug ELSE w8_.widgetgeneral_slug END) AS sclr9, t10_.tree_misc_value_text AS tree_misc_value_text10, t11_.tree_misc_value_text AS tree_misc_value_text11
FROM tree_relation t12_
INNER JOIN tree t4_ ON t12_.tree_relation_parent = t4_.tree_id
INNER JOIN treetype t3_ ON t4_.tree_type_id = t3_.treetype_id AND (t3_.treetype_name IN ('global', 'country'))
INNER JOIN contentelement c13_ ON t4_.tree_id = c13_.contentelement_tree_id
INNER JOIN contentleaf c14_ ON c13_.contentelement_contentleaf_id = c14_.contentleaf_id AND (c14_.contentleaf_contentbranch_id = 1)
INNER JOIN widgetgeneral w9_
INNER JOIN widgetabstract w15_ ON w9_.widgetabstract_id = w15_.widgetabstract_id AND (w15_.widgetabstract_contentelement_id = c13_.contentelement_id AND w15_.widgetabstract_discriminator IN ('general') AND w15_.widgetabstract_state = 'preview')
INNER JOIN tree t0_ ON t12_.tree_relation_child = t0_.tree_id
INNER JOIN treetype t1_ ON t0_.tree_type_id = t1_.treetype_id AND (t1_.treetype_name IN ('city','region'))
INNER JOIN contentelement c2_ ON t0_.tree_id = c2_.contentelement_tree_id
INNER JOIN contentleaf c16_ ON c2_.contentelement_contentleaf_id = c16_.contentleaf_id AND (c16_.contentleaf_contentbranch_id = 1)
INNER JOIN widgetgeneral w8_
INNER JOIN widgetabstract w17_ ON w8_.widgetabstract_id = w17_.widgetabstract_id AND (w17_.widgetabstract_contentelement_id = c2_.contentelement_id AND w17_.widgetabstract_discriminator IN ('general') AND w17_.widgetabstract_state = 'preview')
INNER JOIN widgetgeneral w18_
INNER JOIN widgetabstract w19_ ON w18_.widgetabstract_id = w19_.widgetabstract_id AND (w19_.widgetabstract_contentleaf_id = c16_.contentleaf_id AND w19_.widgetabstract_discriminator IN ('general') AND w19_.widgetabstract_state = 'preview')
LEFT JOIN picture p5_ ON t0_.tree_picture_id = p5_.picture_id
LEFT JOIN tree_misc t6_ ON t0_.tree_id = t6_.tree_misc_tree_id AND (t6_.tree_misc_attributetype_key = 'flagId')
LEFT JOIN tree_misc t7_ ON t4_.tree_id = t7_.tree_misc_tree_id AND (t7_.tree_misc_attributetype_key = 'flagId')
LEFT JOIN tree_misc t10_ ON t0_.tree_id = t10_.tree_misc_tree_id AND (t10_.tree_misc_attributetype_key = 'latitude')
LEFT JOIN tree_misc t11_ ON t0_.tree_id = t11_.tree_misc_tree_id AND (t11_.tree_misc_attributetype_key = 'longitude')
WHERE w17_.widgetabstract_visibility = 'active' OR (w17_.widgetabstract_visibility = 'parent' AND w19_.widgetabstract_visibility = 'active')

and the explain for 5.7.:

we tried upgrade as well as complete blank installation. switched on and off all sql modes and query optimizier options. if you need more information or server variables let me know.

os: Debian GNU/Linux 8 (jessie) server version is: 5.7.14-7-log Percona Server (GPL), Release '7', Revision '083e298'

maybe you have a hint what we are missing.

edit: adding config

[mysqld]
port                            = 3306
user                            = mysql
socket                          = /var/run/mysqld/mysqld.sock
pid-file                        = /var/run/mysqld/mysqld.pid
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /tmp
lc-messages-dir                 = /usr/share/mysql
max_connect_errors              = 1000000
log-error                       = /var/log/mysql/error.log
skip-external-locking
myisam-recover-options          = BACKUP
character-set-server            = utf8
collation-server                = utf8_general_ci
interactive_timeout             = 28800
wait_timeout                    = 28800
skip-name-resolve
group_concat_max_len            = 268435456

innodb_file_per_table
innodb_buffer_pool_size               = 48G
innodb_buffer_pool_instances          = 1
innodb_flush_log_at_trx_commit        = 1
innodb_data_file_path                 = ibdata1:2G:autoextend
innodb_log_file_size                  = 256M
innodb_log_buffer_size                = 64M
innodb_file_format                    = barracuda
innodb_flush_method                   = O_DIRECT[mysqld_safe]
syslog
numa_interleave

# Per Thread
sort_buffer_size        = 4M
read_buffer_size        = 2M

# Cache/connection relevant
thread_cache_size       = 850
table_open_cache        = 4048
max_connections         = 1300

# MyISAM settings (also valid for queries with temporary tables)
key_buffer_size         = 128M
myisam_sort_buffer_size = 16M

# Misc
max_allowed_packet      = 256M
max_heap_table_size     = 16M
thread_stack            = 192K
tmp_table_size          = 16M

# Query cache
query_cache_limit       = 5M
query_cache_size        = 4024M

server-id              = 102
log_bin                = /var/log/mysql/mysql-bin.log
binlog_format          = mixed
expire_logs_days       = 10
max_binlog_size        = 100M
# enforce syncing of every transation to binlog (crash safe, with bbu this should be fast)
sync_binlog            = 1
sync_relay_log         = 1
sync_master_info       = 1
sync_relay_log_info    = 1
relay-log              = mysqld-relay-bin
skip-slave-start
log-slave-updates

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 1
log-queries-not-using-indexes

edit 2: add explain for 5.5

Answer 1

The new join ordering is probably due to MySQL 5.7 overestimating the effect of the filtering that can be made based on the WHERE and ON clauses. In MySQL 5.6, filtering was not taken into account which often caused an unnecessary expensive join ordering to be chosen. In general, MySQL 5.7 will often be able to find a better join ordering by taking filtering into account. However, for conditions on un-indexed columns, the filtering estimate is just a guess that may not work well for conditions that are not very selective.

You may revert to 5.6 behavior by setting optimizer_switch='condition_fanout_filter=off', or you can use STRAIGHT_JOIN to force a specific join ordering.