Re: query with bad performance after migration to mysql 5.6

[walter verdelocco]

this is the explain in json format :

EXPLAIN FORMAT=JSON
-> 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;

| {
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "messaggio",
"access_type": "ALL",
"possible_keys": [
"PRIMARY",
"idpadre",
"idpadrepubblica_idx",
"ididpadrepubblica"
],
"rows": 2445095,
"filtered": 100
}
},
{
"table": {
"table_name": "allegati",
"access_type": "ref",
"possible_keys": [
"idpost_idx"
],
"key": "idpost_idx",
"used_key_parts": [
"idpost"
],
"key_length": "5",
"ref": [
"DROPME_ts_mms56.messaggio.id"
],
"rows": 1,
"filtered": 100,
"using_index": true
}
},
{
"table": {
"table_name": "m_padre",
"access_type": "ALL",
"rows": 26,
"filtered": 100,
"using_join_buffer": "Block Nested Loop",
"attached_condition": "((`m_padre`.`id` = `DROPME_ts_mms56`.`messaggio`.`idpadre`) or (`m_padre`.`id` = `DROPME_ts_mms56`.`messaggio`.`id`))",
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "m",
"access_type": "ref",
"possible_keys": [
"idpadre",
"idpadrepubblica_idx",
"idarticolo_idx"
],
"key": "idarticolo_idx",
"used_key_parts": [
"idarticolo"
],
"key_length": "130",
"ref": [
"const"
],
"rows": 26,
"filtered": 100,
"index_condition": "(`DROPME_ts_mms56`.`m`.`idarticolo` = 'repnews_271330')",
"attached_condition": "((`DROPME_ts_mms56`.`m`.`idarticolo` <=> 'repnews_271330') and ((`DROPME_ts_mms56`.`m`.`pubblica` = 1) and (`DROPME_ts_mms56`.`m`.`idpadre` = 0)))"
}
}
}
}
}
}
]
}
}
} |

1 row in set, 1 warning (0.00 sec)