Page MenuHomePhabricator
Paste P13364

pc2007 optimizer
ActivePublic

Authored by Marostegui on Nov 23 2020, 11:51 AM.
mysql:root@localhost [parsercache]> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
QUERY: DELETE FROM `pc221` WHERE (exptime < '20201121180630') AND keyname IN ('wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47172141','ruwikinews:pcache:idoptions:2274652','plwiki:stable-pcache:idhash:2985160-0!canonical','commonswiki:pcache:idhash:28470463-0!wb=3','eswiki:pcache:idhash:8407454-0!canonical','wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47171328','cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0','wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3','zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs','commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0','cswiki:pcache:idhash:238465-0!canonical','arwiki:stable-pcache:idoptions:5577217','ruwikinews:pcache:idhash:2275122-0!canonical','wikidatawiki:pcache:idoptions:47170426','commonswiki:pcache:idhash:67472202-0!canonical','frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0','avwiki:pcache:idoptions:5377','svwiki:pcache:idhash:3766069-0!canonical','wikidatawiki:pcache:idoptions:47170137','wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idoptions:47170029','avwiki:pcache:idhash:5154-0!canonical','itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0','azbwiki:pcache:idoptions:215428','wikidatawiki:pcache:idoptions:47169595','azbwiki:pcache:idhash:217308-0!canonical','commonswiki:pcache:idhash:76967381-1!userlang=sco','barwiki:pcache:idoptions:54173','commonswiki:pcache:idhash:63992420-0!wb=3','commonswiki:pcache:idoptions:36008242','commonswiki:pcache:idhash:14182321-0!wb=3','commonswiki:pcache:idhash:14182500-0!wb=3','dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3','svwiki:pcache:idoptions:6396670','commonswiki:pcache:idhash:2234685-1!userlang=fr','commonswiki:pcache:idoptions:13832128','ruwikinews:pcache:idoptions:2275409','commonswiki:pcache:idoptions:85397714','wikidatawiki:pcache:idoptions:45230319','ruwikinews:pcache:idoptions:2275772','wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3','wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3','bgwikiquote:pcache:idhash:5470-0!canonical','bgwikiquote:pcache:idoptions:5475','enwiki:pcache:idhash:30141328-0!canonical','wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3','eswiktionary:pcache:idhash:461899-0!canonical','enwiki:pcache:idhash:33991562-0!thumbsize=3','commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3','ruwikinews:pcache:idhash:2276526-0!canonical','commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3','bnwikisource:pcache:idhash:759895-0!canonical','commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0','bowiki:pcache:idhash:5208-0!canonical','cswiki:pcache:idhash:799292-0!canonical','eswiktionary:pcache:idoptions:462181','commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3','commonswiki:pcache:idhash:39411274-0!canonical','commonswiki:pcache:idoptions:39411310','wikidatawiki:pcache:idoptions:47138756','cswiki:pcache:idoptions:739306','idwiktionary:pcache:idoptions:44095','srwiki:pcache:idoptions:1563379','commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3','commonswiki:pcache:idhash:74084336-0!wb=3','wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3','bswiki:pcache:idoptions:195228','bpywiki:pcache:idoptions:45261','commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0','bswiktionary:pcache:idhash:4994-0!canonical','ruwikinews:pcache:idoptions:2277615','commonswiki:pcache:idhash:30387844-1!userlang=es','cawiki:pcache:idoptions:510277','cawiki:pcache:idoptions:542825','commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0','commonswiki:pcache:idhash:3706602-1!userlang=ms','jawiki:pcache:idoptions:372412','wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3','cawiki:pcache:idoptions:539285','wikidatawiki:pcache:idoptions:47137123','commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0','zhwikisource:pcache:idhash:284908-0!canonical!zh','eswiktionary:pcache:idhash:463096-0!canonical','commonswiki:pcache:idhash:56623174-0!userlang=it','cawiki:pcache:idhash:539521-0!canonical','commonswiki:pcache:idhash:54662835-0!canonical','wikidatawiki:pcache:idoptions:47136275','wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3','commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0','eswiktionary:pcache:idhash:463367-0!canonical','eswiktionary:pcache:idhash:463413-0!canonical','ruwikinews:pcache:idoptions:2279056','hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0','ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0','commonswiki:pcache:idhash:94865712-0!wb=3','ruwikinews:pcache:idhash:2280030-0!canonical','commonswiki:pcache:idhash:13651795-0!wb=3')
/*!*/
TRACE: {
"steps": [
{
"table": "pc221",
"range_analysis": {
"table_scan": {
"rows": 533021,
"cost": 810035
},
"potential_range_indexes": [
{
"index": "keyname",
"usable": true,
"key_parts": ["keyname"]
},
{
"index": "exptime",
"usable": true,
"key_parts": ["exptime", "keyname"]
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "no join"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "keyname",
"ranges": [
"(arwiki:stable-pcache:idoptions:5577217) <= (keyname) <= (arwiki:stable-pcache:idoptions:5577217)",
"(avwiki:pcache:idhash:5154-0!canonical) <= (keyname) <= (avwiki:pcache:idhash:5154-0!canonical)",
"(avwiki:pcache:idoptions:5377) <= (keyname) <= (avwiki:pcache:idoptions:5377)",
"(azbwiki:pcache:idhash:217308-0!canonical) <= (keyname) <= (azbwiki:pcache:idhash:217308-0!canonical)",
"(azbwiki:pcache:idoptions:215428) <= (keyname) <= (azbwiki:pcache:idoptions:215428)",
"(barwiki:pcache:idoptions:54173) <= (keyname) <= (barwiki:pcache:idoptions:54173)",
"(bgwikiquote:pcache:idhash:5470-0!canonical) <= (keyname) <= (bgwikiquote:pcache:idhash:5470-0!canonical)",
"(bgwikiquote:pcache:idoptions:5475) <= (keyname) <= (bgwikiquote:pcache:idoptions:5475)",
"(bnwikisource:pcache:idhash:759895-0!canonical) <= (keyname) <= (bnwikisource:pcache:idhash:759895-0!canonical)",
"(bowiki:pcache:idhash:5208-0!canonical) <= (keyname) <= (bowiki:pcache:idhash:5208-0!canonical)",
"(bpywiki:pcache:idoptions:45261) <= (keyname) <= (bpywiki:pcache:idoptions:45261)",
"(bswiki:pcache:idoptions:195228) <= (keyname) <= (bswiki:pcache:idoptions:195228)",
"(bswiktionary:pcache:idhash:4994-0!canonical) <= (keyname) <= (bswiktionary:pcache:idhash:4994-0!canonical)",
"(cawiki:pcache:idhash:539521-0!canonical) <= (keyname) <= (cawiki:pcache:idhash:539521-0!canonical)",
"(cawiki:pcache:idoptions:510277) <= (keyname) <= (cawiki:pcache:idoptions:510277)",
"(cawiki:pcache:idoptions:539285) <= (keyname) <= (cawiki:pcache:idoptions:539285)",
"(cawiki:pcache:idoptions:542825) <= (keyname) <= (cawiki:pcache:idoptions:542825)",
"(cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0) <= (keyname) <= (cawikisource:pcache:idhash:21311-0!canonical!responsiveimages=0)",
"(commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:10866377-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:13651795-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:13651795-0!wb=3)",
"(commonswiki:pcache:idhash:14182321-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182321-0!wb=3)",
"(commonswiki:pcache:idhash:14182500-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:14182500-0!wb=3)",
"(commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:14942870-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:2234685-1!userlang=fr) <= (keyname) <= (commonswiki:pcache:idhash:2234685-1!userlang=fr)",
"(commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:23975758-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:28470463-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:28470463-0!wb=3)",
"(commonswiki:pcache:idhash:30387844-1!userlang=es) <= (keyname) <= (commonswiki:pcache:idhash:30387844-1!userlang=es)",
"(commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:33376678-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:3706602-1!userlang=ms) <= (keyname) <= (commonswiki:pcache:idhash:3706602-1!userlang=ms)",
"(commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:37851180-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:39411274-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:39411274-0!canonical)",
"(commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40358720-1!userlang=de!wb=3)",
"(commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:40914-1!userlang=mk!wb=3)",
"(commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:53947269-1!userlang=sco!wb=3)",
"(commonswiki:pcache:idhash:54662835-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:54662835-0!canonical)",
"(commonswiki:pcache:idhash:56623174-0!userlang=it) <= (keyname) <= (commonswiki:pcache:idhash:56623174-0!userlang=it)",
"(commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63431106-1!userlang=it!wb=3)",
"(commonswiki:pcache:idhash:63992420-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:63992420-0!wb=3)",
"(commonswiki:pcache:idhash:67472202-0!canonical) <= (keyname) <= (commonswiki:pcache:idhash:67472202-0!canonical)",
"(commonswiki:pcache:idhash:74084336-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:74084336-0!wb=3)",
"(commonswiki:pcache:idhash:76967381-1!userlang=sco) <= (keyname) <= (commonswiki:pcache:idhash:76967381-1!userlang=sco)",
"(commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:8549664-1!userlang=de!wb=3)",
"(commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0) <= (keyname) <= (commonswiki:pcache:idhash:8828291-0!wb=3!responsiveimages=0)",
"(commonswiki:pcache:idhash:94865712-0!wb=3) <= (keyname) <= (commonswiki:pcache:idhash:94865712-0!wb=3)",
"(commonswiki:pcache:idoptions:13832128) <= (keyname) <= (commonswiki:pcache:idoptions:13832128)",
"(commonswiki:pcache:idoptions:36008242) <= (keyname) <= (commonswiki:pcache:idoptions:36008242)",
"(commonswiki:pcache:idoptions:39411310) <= (keyname) <= (commonswiki:pcache:idoptions:39411310)",
"(commonswiki:pcache:idoptions:85397714) <= (keyname) <= (commonswiki:pcache:idoptions:85397714)",
"(cswiki:pcache:idhash:238465-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:238465-0!canonical)",
"(cswiki:pcache:idhash:799292-0!canonical) <= (keyname) <= (cswiki:pcache:idhash:799292-0!canonical)",
"(cswiki:pcache:idoptions:739306) <= (keyname) <= (cswiki:pcache:idoptions:739306)",
"(dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0) <= (keyname) <= (dewikinews:pcache:idhash:76526-0!canonical!responsiveimages=0)",
"(enwiki:pcache:idhash:30141328-0!canonical) <= (keyname) <= (enwiki:pcache:idhash:30141328-0!canonical)",
"(enwiki:pcache:idhash:33991562-0!thumbsize=3) <= (keyname) <= (enwiki:pcache:idhash:33991562-0!thumbsize=3)",
"(eswiki:pcache:idhash:8407454-0!canonical) <= (keyname) <= (eswiki:pcache:idhash:8407454-0!canonical)",
"(eswiktionary:pcache:idhash:461899-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:461899-0!canonical)",
"(eswiktionary:pcache:idhash:463096-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463096-0!canonical)",
"(eswiktionary:pcache:idhash:463367-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463367-0!canonical)",
"(eswiktionary:pcache:idhash:463413-0!canonical) <= (keyname) <= (eswiktionary:pcache:idhash:463413-0!canonical)",
"(eswiktionary:pcache:idoptions:462181) <= (keyname) <= (eswiktionary:pcache:idoptions:462181)",
"(frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0) <= (keyname) <= (frwikisource:pcache:idhash:1826203-0!canonical!responsiveimages=0)",
"(hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0) <= (keyname) <= (hrwikisource:pcache:idhash:16642-0!canonical!responsiveimages=0)",
"(idwiktionary:pcache:idoptions:44095) <= (keyname) <= (idwiktionary:pcache:idoptions:44095)",
"(itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0) <= (keyname) <= (itwiki:pcache:idhash:3499567-0!numberheadings=1!responsiveimages=0)",
"(jawiki:pcache:idoptions:372412) <= (keyname) <= (jawiki:pcache:idoptions:372412)",
"(plwiki:stable-pcache:idhash:2985160-0!canonical) <= (keyname) <= (plwiki:stable-pcache:idhash:2985160-0!canonical)",
"(ruwikinews:pcache:idhash:2275122-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2275122-0!canonical)",
"(ruwikinews:pcache:idhash:2276526-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2276526-0!canonical)",
"(ruwikinews:pcache:idhash:2280030-0!canonical) <= (keyname) <= (ruwikinews:pcache:idhash:2280030-0!canonical)",
"(ruwikinews:pcache:idoptions:2274652) <= (keyname) <= (ruwikinews:pcache:idoptions:2274652)",
"(ruwikinews:pcache:idoptions:2275409) <= (keyname) <= (ruwikinews:pcache:idoptions:2275409)",
"(ruwikinews:pcache:idoptions:2275772) <= (keyname) <= (ruwikinews:pcache:idoptions:2275772)",
"(ruwikinews:pcache:idoptions:2277615) <= (keyname) <= (ruwikinews:pcache:idoptions:2277615)",
"(ruwikinews:pcache:idoptions:2279056) <= (keyname) <= (ruwikinews:pcache:idoptions:2279056)",
"(srwiki:pcache:idoptions:1563379) <= (keyname) <= (srwiki:pcache:idoptions:1563379)",
"(svwiki:pcache:idhash:3766069-0!canonical) <= (keyname) <= (svwiki:pcache:idhash:3766069-0!canonical)",
"(svwiki:pcache:idoptions:6396670) <= (keyname) <= (svwiki:pcache:idoptions:6396670)",
"(ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0) <= (keyname) <= (ukwiki:pcache:idhash:23941-0!canonical!responsiveimages=0)",
"(wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47136192-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47137185-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47138527-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140010-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47140272-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47170029-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171069-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47171317-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:47172131-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3) <= (keyname) <= (wikidatawiki:pcache:idhash:9238068-0!termboxVersion=1!wb=3)",
"(wikidatawiki:pcache:idoptions:45230319) <= (keyname) <= (wikidatawiki:pcache:idoptions:45230319)",
"(wikidatawiki:pcache:idoptions:47136275) <= (keyname) <= (wikidatawiki:pcache:idoptions:47136275)",
"(wikidatawiki:pcache:idoptions:47137123) <= (keyname) <= (wikidatawiki:pcache:idoptions:47137123)",
"(wikidatawiki:pcache:idoptions:47138756) <= (keyname) <= (wikidatawiki:pcache:idoptions:47138756)",
"(wikidatawiki:pcache:idoptions:47169595) <= (keyname) <= (wikidatawiki:pcache:idoptions:47169595)",
"(wikidatawiki:pcache:idoptions:47170029) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170029)",
"(wikidatawiki:pcache:idoptions:47170137) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170137)",
"(wikidatawiki:pcache:idoptions:47170426) <= (keyname) <= (wikidatawiki:pcache:idoptions:47170426)",
"(wikidatawiki:pcache:idoptions:47171328) <= (keyname) <= (wikidatawiki:pcache:idoptions:47171328)",
"(wikidatawiki:pcache:idoptions:47172141) <= (keyname) <= (wikidatawiki:pcache:idoptions:47172141)",
"(zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs) <= (keyname) <= (zhwiki:pcache:idhash:556598-0!canonical!zh-cn!tmh-videojs)",
"(zhwikisource:pcache:idhash:284908-0!canonical!zh) <= (keyname) <= (zhwikisource:pcache:idhash:284908-0!canonical!zh)"
],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 100,
"cost": 120.13,
"chosen": true
},
{
"index": "exptime",
"ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.5033,
"chosen": true
}
],
"analyzing_index_merge_union": []
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "exptime",
"rows": 1,
"ranges": ["(NULL) < (exptime) < (2020-11-21 18:06:30)"]
},
"rows_for_plan": 1,
"cost_for_plan": 2.5033,
"chosen": true
}
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.001 sec)