Last modified: 2011-03-13 18:06:15 UTC
Although __HIDDENCAT__ has been implemented, the cat_hidden field isn't set to 1 for hidden categories. It should be.
Why? I think it should never have been added. It's just a denormalised form of page_props, using it would mean extra unnecessary queries on edit.
This is needed to fix https://bugzilla.wikimedia.org/show_bug.cgi?id=15579 of the API.
(In reply to comment #2) > This is needed to fix https://bugzilla.wikimedia.org/show_bug.cgi?id=15579 of > the API. > No it isn't. The API can use page_props, same as everyone else.
(In reply to comment #3) > (In reply to comment #2) > > This is needed to fix https://bugzilla.wikimedia.org/show_bug.cgi?id=15579 of > > the API. > > > > No it isn't. The API can use page_props, same as everyone else. > Actually, it *is* necessary if we want non-filesorting queries in the API. If cat_hidden were enabled, the API could simply do: SELECT cl_from,cl_to FROM categorylinks, category WHERE cat_title = cl_to AND cl_from IN('85', '92', '111', '138') AND cat_hidden='0' ORDER BY cl_from, cl_to; (or cat_hidden='1') which runs just fine (except of course for the fact that cat_hidden is always 0, which is what this bug is about). With page_props, that query becomes: SELECT cl_from,cl_to FROM categorylinks LEFT JOIN page ON page_namespace = '14' AND page_title = cl_to LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'hiddencat' WHERE cl_from IN ('85', '92', '111', '138') AND pp_propname IS NULL ORDER BY cl_from, cl_to; (or pp_propname IS NOT NULL) The IS NULL version runs fine, but the IS NOT NULL version filesorts.
For the "IS NOT NULL" case, could you use plain JOINs instead of LEFT JOINs, since it will only pass when the joins succeed? Or does that filesort too?
(In reply to comment #5) > For the "IS NOT NULL" case, could you use plain JOINs instead of LEFT JOINs, > since it will only pass when the joins succeed? Or does that filesort too? > Hadn't thought of that, but it doesn't eliminate the filesort.
Ok, I downloaded the dumps of these three tables from http://download.wikimedia.org/enwiki/ to really try this out. It seems that MySQL is insisting on scanning through every category in page first and then joining categorylinks to that, instead of getting matching rows from categorylinks and doing an eq_ref on page (which would avoid the filesort). If we force MySQL to use our specified join order, it seems to work. SELECT /*! STRAIGHT_JOIN */ cl_from,cl_to FROM categorylinks LEFT JOIN page ON page_namespace = '14' AND page_title = cl_to LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'hiddencat' WHERE cl_from IN ('85', '92', '111', '138') AND pp_propname IS NOT NULL ORDER BY cl_from, cl_to; $this->addOption('STRAIGHT_JOIN') should produce a query like that.
(In reply to comment #7) > Ok, I downloaded the dumps of these three tables from > http://download.wikimedia.org/enwiki/ to really try this out. It seems that > MySQL is insisting on scanning through every category in page first and then > joining categorylinks to that, instead of getting matching rows from > categorylinks and doing an eq_ref on page (which would avoid the filesort). If > we force MySQL to use our specified join order, it seems to work. > > SELECT /*! STRAIGHT_JOIN */ cl_from,cl_to FROM categorylinks LEFT JOIN page ON > page_namespace = '14' AND page_title = cl_to LEFT JOIN page_props ON pp_page = > page_id AND pp_propname = 'hiddencat' WHERE cl_from IN ('85', '92', '111', > '138') AND pp_propname IS NOT NULL ORDER BY cl_from, cl_to; > > $this->addOption('STRAIGHT_JOIN') should produce a query like that. > Yeah, that seems to work. I'll use that.
