While working on T250060 we realise that the following query uses a FORCE INDEX (tl_namespace):
SELECT /*! STRAIGHT_JOIN */ tl_from,tl_namespace AS `bl_namespace`,tl_title AS `bl_title`,page_title,page_namespace FROM `templatelinks` FORCE INDEX (tl_namespace),`page` WHERE (tl_from = page_id) AND ((tl_namespace = 4 AND tl_title = 'Database_reports/Identified_duplicates/2')) AND page_is_redirect = 0 ORDER BY tl_from LIMIT 501
However, it looks like that the optimizer on 10.1 and 10.4 no longer needs this hint, as it uses the right index:
root@cumin1001:/home/marostegui# cat query EXPLAIN SELECT /*! STRAIGHT_JOIN */ tl_from,tl_namespace AS `bl_namespace`,tl_title AS `bl_title`,page_title,page_namespace FROM `templatelinks`,`page` WHERE (tl_from = page_id) AND ((tl_namespace = 4 AND tl_title = 'Database_reports/Identified_duplicates/2')) AND page_is_redirect = 0 ORDER BY tl_from LIMIT 501 root@cumin1001:/home/marostegui# ./section s8 | while read host port; do echo "=====> $host"; cat query | mysql.py -h$host:$port -BN wikidatawiki ; done =====> labsdb1012.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> labsdb1011.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> labsdb1010.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> labsdb1009.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> dbstore1005.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2100.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2094.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2086.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2085.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2083.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2082.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2081.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2080.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db2079.codfw.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1126.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1124.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1116.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1114.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1111.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1109.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1104.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1101.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1099.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1092.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where =====> db1087.eqiad.wmnet 1 SIMPLE templatelinks ref PRIMARY,tl_namespace tl_namespace 261 const,const 1 Using where; Using index 1 SIMPLE page eq_ref PRIMARY,page_redirect_namespace_len PRIMARY 4 wikidatawiki.templatelinks.tl_from 1 Using where
We should probably remove the FORCE from the code.