Page_props associates key/value pairs with page ids:
`pp_page` int(11) NOT NULL, `pp_propname` varbinary(60) NOT NULL, `pp_value` blob NOT NULL,
Currently, this does not however allow us to efficiently find pages by a given key/value pair. That however would be quite useful, e.g. for marking pages as "stub" upon save, and then listing all stubs.
There are two indexes defined at the moment:
UNIQUE KEY `pp_page_propname` (`pp_page`,`pp_propname`), UNIQUE KEY `pp_propname_page` (`pp_propname`,`pp_page`)
So, we can efficiently get all properties (or one specific property) for a page. And we can efficiently get all pages that *have* a specific property. We can however not get all pages with a specific *value* for a given property. For that, we would need an index over at least pp_propname and pp_value, but this doesn't work, since pp_value is a blob. So allow this, we'd need an id column. I propose to add a column:
`pp_value_id` varbinary(255) NOT NULL
This may contain a hash of the value blob, or it may contain the actual value (with pp_value being
the same, or empty, or null). This would allow us to create an index for looking up pages by value:
UNIQUE KEY `pp_propname_value` (`pp_propname`,`pp_value_id`, `pp_page`)
Perhaps the pp_propname_page could then be dropped, I don't really see a use case for it.
Whiteboard: u=dev c=backend p=13 s=2014-04-23