« Older | Newer » Web site design:

Forums search on UKClimbing

by Nick Smith 10th November 2008

Example page from UKC UKClimbing.com has always had a very efficient and fast design, but as the popular forums have got more and more busy, the database behind the forums has grown to over 3 million messages and 2Gb+ of data.

We recently split the database into more manageable chunks - most activity is done on threads from the last 6 months (still 240,000 messages!). That 120Mb table changes every few seconds, but can be cached in memory on the server, and is very fast.

Older threads are split into separate tables of approximately 240Mb (plus another 140Mb of indexes) each, and we use the MySQL MERGE table facility to work on the entire set of threads, if required. These older thread tables change far less frequently, and are much easier to back up.

At the same time we've dramatically improved the quality and speed of the forums searching facility:

Default searches use just the most recent 6 months of threads, but...
You can choose to search back through the entire archive, back to 2000
Searching is MUCH faster, using MySQL FULLTEXT indexing
If you enter more than 1 word, it now searches for both words (in either order), rather than either word. So climbing stanage will look for climbing AND stanage, instead of climbing OR stanage. This gives more useful results
climbing -grit lists messages that have climbing but not grit
"stanage HVS" lists messages that have the exact phrase stanage HVS
The help text is more obvious, rather than hidden away under a tiny 'Advanced Search' link

Read more: UKC Search Forums
Permalink | Quiklnk | Email