Selecting pages by tags or custom fields now blazingly fast

by michal-frackowiak on 04 May 2016 14:38

Last week we have re-shaped our database and made several performance improvements that should directly affect your sites, especially if you use tag and custom field selectors within the ListPages module. Even if not — your sites should appear a bit snappier too since the general load on the databases has decreased significantly.

Here's what should be faster now:

Selecting by tags

There are several large sites on Wikidot that depend on tags for categorizing pages. Take Wikiwealth for example. It uses tags to associate numerous data with appropriate market instruments and then invokes ListPages to render useful information to the visitor. The data is so large that every time we had been checking database status there had always been a few queries related to WikiWealth hanging around.

We have basically re-written parts of Wikidot engine that deals with storing tags and selecting pages by tags. We have created new fields on page tables in the database and built indexes on it. As a result the queries that used to take 0.5 seconds now take 0.002 seconds to execute.

Our own issue tracker, built on tags, works much nicer too. It selects pages by tags, e.g.

[[module CountPages category="issue operation support packaging" tags="_wip"]]

Our main page contains 15 ListPages and CountPages modules. After the database redesign its content is rendered significantly faster.

If your site uses any complex ListPages or CountPages construct that selects pages by tags, you will likely see an improvement too.

Selecting by custom fields

If your site contains form-enabled pages and you use custom field selectors in ListPages or CountPages or you order results by custom fields — there are several improvements too. We have decided to store all page attributes in a separate table using database type designed to store flat key-value data (HStore).

Selecting pages by custom fields has never been optimized since its introduction a few years ago.
One of the new sites that take advantage of form-enabled data is DarkSouls3 wiki. It uses data-enabled pages to categorize game items. For example their Weapons page uses several ListPages modules to list all items (pages) categorized as Axes, Bows, Swords etc. The database optimizations we've done reduced specific query times from 0.8s to 0.008s. It makes a huge difference when working with the content or viewing it.

Any changes in user interface?

No, the changes are limited to backend only. No changes should have been introduced that would affect results returned by ListPages nor CountPages. If, by any chance, your site displays results other than you expect, please let us know. Although we've extensively tested the improvements there might be a few quirks we missed.

Tags and custom fields in data forms can now scale much better as sites grow. If you plan on creating sites with structured data, even storing thousands of items, I suggest you should give data forms a try.

Read more at:

Comments: 21

Add a New Comment