|
Here's my observations with 10.4.11 (Release version).
1. Running EEXPLAIN just after population of the tables does not make sense. Apparently InnoDB does not have proper statistical data at this moment. The output of the EXPLAIN shows this:
MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
| 1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index |
|
| 1 | SIMPLE | track | ref | PRIMARY,label_id,enabled_2,enabled | enabled_2 | 1 | const | 1 | |
|
| 1 | SIMPLE | label | eq_ref | PRIMARY,enabled | PRIMARY | 4 | test.track.label_id | 1 | Using where |
|
| 1 | SIMPLE | license_track | eq_ref | license_id,track_id,license_id_2 | license_id | 8 | test.subscription.license_id,test.track.id | 1 | Using index |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
and the output of the following queries
MariaDB [test]> select count(*) from track;
|
+----------+
|
| count(*) |
|
+----------+
|
| 39349 |
|
+----------+
|
1 row in set (0.021 sec)
|
|
MariaDB [test]> select count(distinct enabled) from track;
|
+-------------------------+
|
| count(distinct enabled) |
|
+-------------------------+
|
| 2 |
|
+-------------------------+
|
2. After we having run the select once we have a different execution plan:
MariaDB [test]> SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
|
+-----------------+
|
| COUNT(label.id) |
|
+-----------------+
|
| 0 |
|
+-----------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
| 1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index |
|
| 1 | SIMPLE | label | ref | PRIMARY,enabled | enabled | 1 | const | 66 | Using index |
|
| 1 | SIMPLE | track | ref | PRIMARY,label_id,enabled_2,enabled | label_id | 4 | test.label.id | 1 | Using where |
|
| 1 | SIMPLE | license_track | eq_ref | license_id,track_id,license_id_2 | license_id | 8 | test.subscription.license_id,test.track.id | 1 | Using index |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+--------------------------------------------+------+-------------+
|
4 rows in set (0.001 sec)
|
ANALYZE FORMAT=JSON shows us that this is a pretty good execution plan:
MariaDB [test]> ANALYZE FORMAT=JSON SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id \G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4006,
|
"table": {
|
"table_name": "subscription",
|
"access_type": "ref",
|
"possible_keys": ["account_id", "license_id", "account_id_2"],
|
"key": "account_id",
|
"key_length": "4",
|
"used_key_parts": ["account_id"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0238,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "label",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "enabled"],
|
"key": "enabled",
|
"key_length": "1",
|
"used_key_parts": ["enabled"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 66,
|
"r_rows": 66,
|
"r_total_time_ms": 0.0509,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "track",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "label_id", "enabled_2", "enabled"],
|
"key": "label_id",
|
"key_length": "4",
|
"used_key_parts": ["label_id"],
|
"ref": ["test.label.id"],
|
"r_loops": 66,
|
"rows": 1,
|
"r_rows": 0,
|
"r_total_time_ms": 0.2507,
|
"filtered": 50,
|
"r_filtered": 100,
|
"attached_condition": "track.enabled = 1"
|
},
|
"table": {
|
"table_name": "license_track",
|
"access_type": "eq_ref",
|
"possible_keys": ["license_id", "track_id", "license_id_2"],
|
"key": "license_id",
|
"key_length": "8",
|
"used_key_parts": ["license_id", "track_id"],
|
"ref": ["test.subscription.license_id", "test.track.id"],
|
"r_loops": 0,
|
"rows": 1,
|
"r_rows": null,
|
"filtered": 100,
|
"r_filtered": null,
|
"using_index": true
|
}
|
}
|
}
|
1 row in set (0.001 sec)
|
Yet again we see that this plan used wrong statistical data:
MariaDB [test]> select count(distinct label_id) from track;
|
+--------------------------+
|
| count(distinct label_id) |
|
+--------------------------+
|
| 3 |
|
+--------------------------+
|
1 row in set (0.011 sec)
|
3. Let's run ANALYZE for all 4 tables:
MariaDB [test]> ANALYZE TABLE label, license_track, subscription, track;
|
+--------------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+--------------------+---------+----------+----------+
|
| test.label | analyze | status | OK |
|
| test.license_track | analyze | status | OK |
|
| test.subscription | analyze | status | OK |
|
| test.track | analyze | status | OK |
|
+--------------------+---------+----------+----------+
|
and run EXPLAIN again:
MariaDB [test]> explain SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id;
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+------------------------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+------------------------------+-------+-------------+
|
| 1 | SIMPLE | subscription | ref | account_id,license_id,account_id_2 | account_id | 4 | const | 1 | Using index |
|
| 1 | SIMPLE | license_track | ref | license_id,track_id,license_id_2 | license_id | 4 | test.subscription.license_id | 20055 | Using index |
|
| 1 | SIMPLE | track | eq_ref | PRIMARY,label_id,enabled_2,enabled | PRIMARY | 4 | test.license_track.track_id | 1 | Using where |
|
| 1 | SIMPLE | label | eq_ref | PRIMARY,enabled | PRIMARY | 4 | test.track.label_id | 1 | Using where |
|
+------+-------------+---------------+--------+------------------------------------+------------+---------+------------------------------+-------+-------------+
|
4 rows in set (0.001 sec)
|
This plan exactly the one called "correct" by Monty though it's by far not as fast as the previous plan:
MariaDB [test]> ANALYZE FORMAT=JSON SELECT SQL_NO_CACHE COUNT(label.id) FROM subscription,label,track,license_track WHERE track.enabled=1 AND label.enabled=0 AND subscription.account_id=7 and license_track.license_id=subscription.license_id and track.id=license_track.track_id and label.id=track.label_id \G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 57.609,
|
"table": {
|
"table_name": "subscription",
|
"access_type": "ref",
|
"possible_keys": ["account_id", "license_id", "account_id_2"],
|
"key": "account_id",
|
"key_length": "4",
|
"used_key_parts": ["account_id"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0281,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "license_track",
|
"access_type": "ref",
|
"possible_keys": ["license_id", "track_id", "license_id_2"],
|
"key": "license_id",
|
"key_length": "4",
|
"used_key_parts": ["license_id"],
|
"ref": ["test.subscription.license_id"],
|
"r_loops": 1,
|
"rows": 20055,
|
"r_rows": 39208,
|
"r_total_time_ms": 12.641,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "track",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "label_id", "enabled_2", "enabled"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.license_track.track_id"],
|
"r_loops": 39208,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 32.887,
|
"filtered": 50,
|
"r_filtered": 99.992,
|
"attached_condition": "track.enabled = 1"
|
},
|
"table": {
|
"table_name": "label",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "enabled"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id"],
|
"ref": ["test.track.label_id"],
|
"r_loops": 39205,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0584,
|
"filtered": 95.652,
|
"r_filtered": 0,
|
"attached_condition": "label.enabled = 0"
|
}
|
}
|
}
|
1 row in set (0.059 sec)
|
|
|
Let's figure out why the optimizer hasn't t chosen the fast plan.
Optimizer trace show us when evaluating plans with the prefix ["subscription", "label", "track"]
"rows_for_plan": 66,
|
"cost_for_plan": 15.404,
|
"rest_of_plan": [
|
{
|
"plan_prefix": ["subscription", "label"],
|
"table": "track",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "label_id",
|
"rows": 6624,
|
"cost": 55978,
|
"chosen": true
|
},
|
{
|
"access_type": "ref",
|
"index": "enabled_2",
|
"used_range_estimates": true,
|
"rows": 19872,
|
"cost": 19272,
|
"chosen": true
|
},
|
{
|
"access_type": "ref",
|
"index": "enabled",
|
"used_range_estimates": true,
|
"rows": 19872,
|
"cost": 19272,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"access_type": "scan",
|
"resulting_rows": 19872,
|
"cost": 4071.4,
|
"chosen": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "scan",
|
"records": 19872,
|
"cost": 4071.4,
|
"uses_join_buffering": true,
|
"filter_used": false
|
}
|
},
|
"rows_for_plan": 1.31e6,
|
"cost_for_plan": 266397,
|
"rest_of_plan": [
|
{
|
"plan_prefix": ["subscription", "label", "track"],
|
"table": "license_track",
|
...
|
It means the optimizer estimates the number of rows the partial join ["subscription", "label", "track"] as ~1300000 (=66*19872). In fact the expected number of rows in this partial join cannot be greater than the number of rows in the table track because this table is joined by the condition label.id=track.label_id and label.id is the primary key of the table label while we expect only 1 row from subscription. So we expect 66 rows from partial join ["subscription", "label"] and each of them has unique label_id.
|