MediaWiki 1.30.0-alpha (3d26a96)
PHP 7.1.1 (apache2handler)
MariaDB 10.1.21-MariaDB
ICU 57.1
Running prio the PR change shows the biggest challenge namely the "rows": 45676, required tablescan and amounts for a 841.3639ms query time.
Query
SELECT DISTINCT smw_title,smw_sortkey,smw_sort,smw_iw FROM `smw_object_ids` INNER JOIN `smw_di_wikipage` AS t1 ON t1.p_id=smw_id WHERE t1.o_id='120299' AND smw_iw!=':smw' AND smw_iw!=':smw-delete' ORDER BY smw_sort 841.3639ms SMWSQLStore3Readers::getInProperties
Query plain (MySQL EXPLAIN)
{
"query_block": {
"select_id": 1,
"temporary_table": {
"function": "buffer",
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": ["o_id", "p_id_2"],
"key": "o_id",
"key_length": "5",
"used_key_parts": ["o_id"],
"ref": ["const"],
"rows": 45676,
"filtered": 100
},
"table": {
"table_name": "smw_object_ids",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "smw_id", "smw_id_2", "smw_iw"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["smw_id"],
"ref": ["mw-master.t1.p_id"],
"rows": 1,
"filtered": 100,
"attached_condition": "((smw_object_ids.smw_iw <> ':smw') and (smw_object_ids.smw_iw <> ':smw-delete'))"
}
}
}
}
SELECT DISTINCT smw_title,smw_sortkey,smw_iw FROM `smw_object_ids` INNER JOIN (SELECT p_id FROM `smw_di_wikipage` WHERE o_id='120299' GROUP BY p_id ) AS t1 ON t1.p_id=smw_id WHERE smw_iw!=':smw' AND smw_iw!=':smw-delete' 39.1431ms SMW\SQLStore\EntityStore\SqlEntityLookupResultFetcher::fetchIncomingProperties
{
"query_block": {
"select_id": 1,
"temporary_table": {
"function": "buffer",
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"rows": 438,
"filtered": 100,
"materialized": {
"query_block": {
"select_id": 2,
"table": {
"table_name": "smw_di_wikipage",
"access_type": "range",
"possible_keys": ["o_id", "o_id_2", "o_id_3"],
"key": "p_id_2",
"key_length": "9",
"used_key_parts": ["p_id", "o_id"],
"rows": 583,
"filtered": 75.129,
"attached_condition": "(smw_di_wikipage.o_id = '120299')",
"using_index_for_group_by": true
}
}
}
},
"table": {
"table_name": "smw_object_ids",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "smw_id", "smw_id_2", "smw_iw", "smw_iw_2"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["smw_id"],
"ref": ["t1.p_id"],
"rows": 1,
"filtered": 100,
"attached_condition": "((smw_object_ids.smw_iw <> ':smw') and (smw_object_ids.smw_iw <> ':smw-delete'))"
}
}
}
}
MediaWiki 1.26.2
PHP 5.6.8 (apache2handler)
PostgreSQL 9.3.12
ICU 54.1
SELECT DISTINCT smw_title,smw_sortkey,smw_sort,smw_iw FROM "smw_object_ids" INNER JOIN "smw_di_wikipage" AS t1 ON t1.p_id=smw_id WHERE t1.o_id='266' AND smw_iw!=':smw' AND smw_iw!=':smw-delete' ORDER BY smw_sort 1282.6319ms SMWSQLStore3Readers::getInProperties
"Unique (cost=15789.36..16182.98 rows=13147 width=299)"
" -> Sort (cost=15789.36..15868.09 rows=31489 width=299)"
" Sort Key: smw_object_ids.smw_sort, smw_object_ids.smw_title, smw_object_ids.smw_sortkey, smw_object_ids.smw_iw"
" -> Merge Join (cost=5202.02..9023.23 rows=31489 width=299)"
" Merge Cond: (smw_object_ids.smw_id = t1.p_id)"
" -> Index Scan using smw_object_ids_index0 on smw_object_ids (cost=0.29..4224.73 rows=29851 width=303)"
" Filter: ((smw_iw <> ':smw'::text) AND (smw_iw <> ':smw-delete'::text))"
" -> Sort (cost=5200.73..5281.77 rows=32418 width=4)"
" Sort Key: t1.p_id"
" -> Bitmap Heap Scan on smw_di_wikipage t1 (cost=671.66..2771.89 rows=32418 width=4)"
" Recheck Cond: (o_id = 266)"
" -> Bitmap Index Scan on smw_di_wikipage_index0 (cost=0.00..663.56 rows=32418 width=0)"
" Index Cond: (o_id = 266)"
SELECT DISTINCT smw_title,smw_sortkey,smw_iw FROM "smw_object_ids" INNER JOIN (SELECT p_id FROM "smw_di_wikipage" WHERE o_id='266' GROUP BY p_id ) AS t1 ON t1.p_id=smw_id WHERE smw_iw!=':smw' AND smw_iw!=':smw-delete' 59.8922ms SMW\SQLStore\EntityStore\SqlEntityLookupResultFetcher::fetchIncomingProperties
"HashAggregate (cost=3339.18..3339.90 rows=72 width=184)"
" -> Nested Loop (cost=2793.58..3338.64 rows=72 width=184)"
" -> HashAggregate (cost=2793.30..2794.04 rows=74 width=4)"
" -> Bitmap Heap Scan on smw_di_wikipage (cost=611.79..2712.21 rows=32434 width=4)"
" Recheck Cond: (o_id = 266)"
" -> Bitmap Index Scan on smw_di_wikipage_index4 (cost=0.00..603.68 rows=32434 width=0)"
" Index Cond: (o_id = 266)"
" -> Index Scan using smw_object_ids_index0 on smw_object_ids (cost=0.29..7.34 rows=1 width=188)"
" Index Cond: (smw_id = smw_di_wikipage.p_id)"
" Filter: ((smw_iw <> ':smw'::text) AND (smw_iw <> ':smw-delete'::text))"