Considerations for assigning fixed property?
- 1. How important is the datatype?
The main consideration for selecting the datatype should be your application needs. If the property value can only be true or false, then it makes sense to be a Boolean. Regarding technical ramifications: roughly speaking, Booleans are simpler than pages, which in turn are simpler than integers and dates, which in turn are simpler than texts. "Simpler" mostly means "should require less disk space and memory", which may (or may not) translate into performance since more query results/indexes fit into memory. Selecting a value uses a single index in every case, so a greater number of fields in the value table does not imply a greater number of comparisons. However, larger data require more comparisons to match a value (the identity of two Booleans is easier to check than the identity of two strings).
- 2. Does it increase the number of joins that the database has to do?
No, I don't think there is any such situation. The use of any property condition in a query always requires one join with the property's table, no matter whether this table is a fixed table or shared with other properties. Special statistics pages (like Special:UnusedProperties) do not query property value tables at all. The use of fixed tables merely reduces the size of the index needed to find the right entries in the table (we don't need to check that the property is the same). This means that comparisons are done with a smaller index structure, which again should lead to performance increases in some cases. (This is what Nischay has already explained above by referring to the smaller number of comparisons.)
There is only one case I can think of where fixed property tables create an extra cost (and only if they are of type wikipage). This happens for value-based queries that do not refer to a specific property. For example, to find all pages that have "Berlin" as a page value for some property (without saying which), one needs to search all property tables for that value, and take the union of all the results (this is not a greater number of joins, but a greater number of queries/unions). The main situation where this type of query is used is in the lower backlinks section of Special:Browse. This only affects fixed property tables with datatype wikipage.
In general, it therefore makes sense to create fixed property tables for all heavily used properties (that usually are not expected to change their datatype). Doing this will save disk space and memory, and can potentially speed up some comparisons at query time (smaller indexes = less things to compare when searching a value). The number of tables that a DBMS can practically handle is limited. A few hundreds should not be a problem, but some ten-thousands would most likely lead to performance degradation. Moreover, a property with less than a thousand values does not need an own table. Fixed property tables are meant for very large wikis.
Super helpful Nischayn and Markus! Thank you! I'm wondering if I could help generate some performance data to help the cause? I would like to switch all of my status flags on WikiApiary to fixed properties Is active, Is audited and probably my collection flags as well like Collect statistics, Collect extension data (see all properties). I'm wondering if there is a way that I could collect some data to help show the benefit of using fixed properties? If there were some before and after I could collect that would be helpful I would happily do that.