|
I tried to reproduce the issue but get little bit different results. On 10.1.28-10.1.31 time of execution was nearly the same for me, around 4-5s. On 10.2.10+10.2.13 it is around 7s, but if I execute it again, then it takes 12-20s.
Query plan was the same, except for examined "rows" for "i2c" table and syntax of parentheses in conditions.
10.2.10
MariaDB [test]> analyze format=json SELECT c.id,count(*) AS COUNT,c.group_id FROM cattypes c LEFT JOIN cattypegroups g ON c.group_id=g.id RIGHT JOIN itemtocattypes i2c ON i2c.cattypeid=c.id LEFT JOIN items i ON i2c.itemid=i.id INNER JOIN itemtocatalogues i2cat ON (i2cat.itemid=i.id AND i2cat.catalogueid=117) WHERE (g.catalogueid=117 OR g.id=378 AND g.ispricegroup) AND i.active=1 AND i.dead=0 GROUP BY c.id;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------
|
| ANALYZE
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 15119,
|
"filesort": {
|
"sort_key": "c.`id`",
|
"r_loops": 1,
|
"r_total_time_ms": 0.0157,
|
"r_used_priority_queue": false,
|
"r_output_rows": 91,
|
"r_buffer_size": "2Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "g",
|
"access_type": "index_merge",
|
"possible_keys": ["PRIMARY", "cat_ord", "id_pricegroup"],
|
"key_length": "5,4",
|
"index_merge": {
|
"sort_union": {
|
"range": {
|
"key": "cat_ord",
|
"used_key_parts": ["catalogueid"]
|
},
|
"range": {
|
"key": "PRIMARY",
|
"used_key_parts": ["id"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 5,
|
"r_rows": 5,
|
"r_total_time_ms": 0.0528,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "g.catalogueid = 117 or g.`id` = 378 and g.ispricegroup <> 0"
|
},
|
"table": {
|
"table_name": "c",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "group_id", "FK_group", "group_ord"],
|
"key": "FK_group",
|
"key_length": "4",
|
"used_key_parts": ["group_id"],
|
"ref": ["test.g.id"],
|
"r_loops": 5,
|
"rows": 9,
|
"r_rows": 20.8,
|
"r_total_time_ms": 0.0937,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "i2c",
|
"access_type": "ref",
|
"possible_keys": ["itemid", "FKIndex1"],
|
"key": "FKIndex1",
|
"key_length": "4",
|
"used_key_parts": ["cattypeid"],
|
"ref": ["test.c.id"],
|
"r_loops": 104,
|
"rows": 82,
|
"r_rows": 3437.8,
|
"r_total_time_ms": 13701,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "id_active_dead"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.i2c.itemid"],
|
"r_loops": 357527,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 963.27,
|
"filtered": 100,
|
"r_filtered": 36.669,
|
"attached_condition": "i.active = 1 and i.dead = 0"
|
},
|
"table": {
|
"table_name": "i2cat",
|
"access_type": "ref",
|
"possible_keys": ["item_cat", "cat_item"],
|
"key": "item_cat",
|
"key_length": "10",
|
"used_key_parts": ["itemid", "catalogueid"],
|
"ref": ["test.i2c.itemid", "const"],
|
"r_loops": 131102,
|
"rows": 1,
|
"r_rows": 0.0688,
|
"r_total_time_ms": 374.1,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
}
|
} |
|
|
10.1.31
MariaDB [test]> analyze format=json SELECT c.id,count(*) AS COUNT,c.group_id FROM cattypes c LEFT JOIN cattypegroups g ON c.group_id=g.id RIGHT JOIN itemtocattypes i2c ON i2c.cattypeid=c.id LEFT JOIN items i ON i2c.itemid=i.id INNER JOIN itemtocatalogues i2cat ON (i2cat.itemid=i.id AND i2cat.catalogueid=117) WHERE (g.catalogueid=117 OR g.id=378 AND g.ispricegroup) AND i.active=1 AND i.dead=0 GROUP BY c.id;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------
|
| ANALYZE
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 3676.3,
|
"filesort": {
|
"r_loops": 1,
|
"r_total_time_ms": 0.0147,
|
"r_used_priority_queue": false,
|
"r_output_rows": 91,
|
"r_buffer_size": "2Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "g",
|
"access_type": "index_merge",
|
"possible_keys": ["PRIMARY", "cat_ord", "id_pricegroup"],
|
"key_length": "5,4",
|
"index_merge": {
|
"sort_union": {
|
"range": {
|
"key": "cat_ord",
|
"used_key_parts": ["catalogueid"]
|
},
|
"range": {
|
"key": "PRIMARY",
|
"used_key_parts": ["id"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 5,
|
"r_rows": 5,
|
"r_total_time_ms": 0.3423,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "((g.catalogueid = 117) or ((g.`id` = 378) and (g.ispricegroup <> 0)))"
|
},
|
"table": {
|
"table_name": "c",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "group_id", "FK_group", "group_ord"],
|
"key": "FK_group",
|
"key_length": "4",
|
"used_key_parts": ["group_id"],
|
"ref": ["test.g.id"],
|
"r_loops": 5,
|
"rows": 9,
|
"r_rows": 20.8,
|
"r_total_time_ms": 0.642,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "i2c",
|
"access_type": "ref",
|
"possible_keys": ["itemid", "FKIndex1"],
|
"key": "FKIndex1",
|
"key_length": "4",
|
"used_key_parts": ["cattypeid"],
|
"ref": ["test.c.id"],
|
"r_loops": 104,
|
"rows": 82,
|
"r_rows": 3437.8,
|
"r_total_time_ms": 2200.3,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "id_active_dead"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.i2c.itemid"],
|
"r_loops": 357527,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 1030.2,
|
"filtered": 100,
|
"r_filtered": 36.669,
|
"attached_condition": "((i.active = 1) and (i.dead = 0))"
|
},
|
"table": {
|
"table_name": "i2cat",
|
"access_type": "ref",
|
"possible_keys": ["item_cat", "cat_item"],
|
"key": "item_cat",
|
"key_length": "10",
|
"used_key_parts": ["itemid", "catalogueid"],
|
"ref": ["test.i2c.itemid", "const"],
|
"r_loops": 131102,
|
"rows": 1,
|
"r_rows": 0.0688,
|
"r_total_time_ms": 365.45,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
}
|
} |
|
10.2.14-MariaDB-debug
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 42568,
|
"filesort": {
|
"sort_key": "c.`id`",
|
"r_loops": 1,
|
"r_total_time_ms": 0.098,
|
"r_used_priority_queue": false,
|
"r_output_rows": 91,
|
"r_buffer_size": "2Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "g",
|
"access_type": "index_merge",
|
"possible_keys": ["PRIMARY", "cat_ord", "id_pricegroup"],
|
"key_length": "5,4",
|
"index_merge": {
|
"sort_union": {
|
"range": {
|
"key": "cat_ord",
|
"used_key_parts": ["catalogueid"]
|
},
|
"range": {
|
"key": "PRIMARY",
|
"used_key_parts": ["id"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 5,
|
"r_rows": 5,
|
"r_total_time_ms": 0.6519,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "g.catalogueid = 117 or g.`id` = 378 and g.ispricegroup <> 0"
|
},
|
"table": {
|
"table_name": "c",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "group_id", "FK_group", "group_ord"],
|
"key": "FK_group",
|
"key_length": "4",
|
"used_key_parts": ["group_id"],
|
"ref": ["test.g.id"],
|
"r_loops": 5,
|
"rows": 9,
|
"r_rows": 20.8,
|
"r_total_time_ms": 0.5217,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "i2c",
|
"access_type": "ref",
|
"possible_keys": ["itemid", "FKIndex1"],
|
"key": "FKIndex1",
|
"key_length": "4",
|
"used_key_parts": ["cattypeid"],
|
"ref": ["test.c.id"],
|
"r_loops": 104,
|
"rows": 91,
|
"r_rows": 3437.8,
|
"r_total_time_ms": 33209,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "id_active_dead"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.i2c.itemid"],
|
"r_loops": 357527,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 4288,
|
"filtered": 100,
|
"r_filtered": 36.669,
|
"attached_condition": "i.active = 1 and i.dead = 0"
|
},
|
"table": {
|
"table_name": "i2cat",
|
"access_type": "ref",
|
"possible_keys": ["item_cat", "cat_item"],
|
"key": "item_cat",
|
"key_length": "10",
|
"used_key_parts": ["itemid", "catalogueid"],
|
"ref": ["test.i2c.itemid", "const"],
|
"r_loops": 131102,
|
"rows": 1,
|
"r_rows": 0.0688,
|
"r_total_time_ms": 4321.7,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
}
|
} |
|
|
|
10.1.10 is fast: 0.04 sec, 10.1.28 and 10.1.15 are slow but seem to use more aggressive cache.
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 41.303,
|
"filesort": {
|
"r_loops": 1,
|
"r_total_time_ms": 0.0372,
|
"r_used_priority_queue": false,
|
"r_output_rows": 91,
|
"r_buffer_size": "2Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "i2cat",
|
"access_type": "ref",
|
"possible_keys": ["item_cat", "cat_item"],
|
"key": "cat_item",
|
"key_length": "5",
|
"used_key_parts": ["catalogueid"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 3682,
|
"r_rows": 3683,
|
"r_total_time_ms": 0.9596,
|
"filtered": 100,
|
"r_filtered": 99.973,
|
"attached_condition": "((i2cat.itemid is not null) and (i2cat.itemid is not null))",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "i",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "id_active_dead"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["dz.i2cat.itemid"],
|
"r_loops": 3682,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 6.9129,
|
"filtered": 100,
|
"r_filtered": 43.292,
|
"attached_condition": "((i.active = 1) and (i.dead = 0))"
|
},
|
"table": {
|
"table_name": "i2c",
|
"access_type": "ref",
|
"possible_keys": ["itemid", "FKIndex1"],
|
"key": "itemid",
|
"key_length": "4",
|
"used_key_parts": ["itemid"],
|
"ref": ["dz.i2cat.itemid"],
|
"r_loops": 1594,
|
"rows": 5,
|
"r_rows": 11.669,
|
"r_total_time_ms": 7.873,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "c",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "group_id", "FK_group", "group_ord"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["dz.i2c.cattypeid"],
|
"r_loops": 18600,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 8.9305,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "g",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "cat_ord", "id_pricegroup"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["dz.c.group_id"],
|
"r_loops": 18600,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 8.7364,
|
"filtered": 100,
|
"r_filtered": 48.473,
|
"attached_condition": "((g.catalogueid = 117) or ((c.group_id = 378) and (g.ispricegroup <> 0)))"
|
}
|
}
|
}
|
}
|
}
|
Main slowness seems to occur whenever sort_union is used.
EDIT:
Did some more testing with SQL_NO_CACHE modifier and 10.1.14 was the last fast version.
Starting from 10.1.15 on my machine, the query time jumps from 0.04s to ~8s, 200 times slower and sort_union appears in the analyze/explain result.
|
dz_reduced2.sql.7z
