Re: query with bad performance after migration to mysql 5.6

[walter verdelocco]

Hi
i try but nothing is changed ....
the query time is always 29 secs !!!

mysql> SELECT @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set optimizer_switch = 'block_nested_loop=off'; Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
mysql>
mysql>
mysql> SELECT messaggio.id, messaggio.data, allegati.id FROM ( select m.id from messaggio m WHERE m.idarticolo='repnews_271330' AND m.idpadre=0 AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50 ) as m_padre join messaggio on (m_padre.id=messaggio.idpadre OR m_padre.id=messaggio.id) left join allegati on messaggio.id = allegati.idpost ORDER BY messaggio.data DESC, messaggio.id desc, allegati.id asc;
+-------+------------+------+
| id | data | id |
+-------+------------+------+
| 20352 | 1169210692 | NULL |
............
........
+-------+------------+------+
27 rows in set (29.27 sec)


w