Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.43, 10.4.12, 10.4.13, 10.4.14, 10.4.15
-
debian
Description
We have the following table:
CREATE TABLE `templatelinks` (
|
`tl_from` int(8) unsigned NOT NULL DEFAULT 0,
|
`tl_namespace` int(11) NOT NULL DEFAULT 0,
|
`tl_title` varbinary(255) NOT NULL DEFAULT '',
|
`tl_from_namespace` int(11) NOT NULL DEFAULT 0,
|
PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
|
KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
|
KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
|
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
|
We have run an analyze table to refresh all the stats.
Having the following query: (pasted here as otherwise it goes above the characters limit for bugs reporting: https://phabricator.wikimedia.org/P13194
The explain for it:
mysql:root@localhost [cswiki]> show explain for 73439158;
|
+------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
|
| 1 | SIMPLE | templatelinks | ref | PRIMARY,tl_namespace | tl_namespace | 4 | const | 1294541 | Using where; Using index |
|
+------+-------------+---------------+------+----------------------+--------------+---------+-------+---------+--------------------------+
|
1 row in set, 1 warning (0.000 sec)
|
However, using a hint like this, changes the query plan drastically:
SELECT / tl_from, tl_title FROM `templatelinks` USE INDEX (PRIMARY) WHERE....
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE templatelinks range PRIMARY PRIMARY 265 NULL 2480 Using where; Using index
|
However, the optimizer keeps choosing the tl_namespace.
This is the optimizer trace for the first and original query: https://phabricator.wikimedia.org/P13220
And the same but with the hint to use the PK: https://phabricator.wikimedia.org/P13221
At a first glance we can see the large difference on scanned rows (and run time):
What the optimizer picks by default
"r_total_time_ms": 162170,
|
"table": {
|
"table_name": "templatelinks",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "tl_namespace"],
|
"key": "tl_namespace",
|
"key_length": "4",
|
"used_key_parts": ["tl_namespace"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1294541,
|
And the runtime and rows if we force the PK to be used:
"r_total_time_ms": 8.3322,
|
"table": {
|
"table_name": "templatelinks",
|
"access_type": "range",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "265",
|
"used_key_parts": ["tl_from", "tl_namespace", "tl_title"],
|
"r_loops": 1,
|
"rows": 2480,
|
The query run time is way different with and without the hint:
And this is true if we try a `USE (PRIMARY) on the query shows way better results:
265 rows in set (0.146 sec)
|
And this is NOT doing USE (PRIMARY) and just leaving the query choosing whatever it prefers (tl_namespace);
265 rows in set (2 min 9.220 sec)
|