#3288 - Major overhaul of fulltext search support, the "Composr fast custom index"

This is a spacer post for a website comment topic. The content this topic relates to: #3288 - Major overhaul of fulltext search support, the "Composr fast custom index"
By Guest posted
I think each existing full text index would need to be replaced with its own new table - we couldn't just use one table. And each table would need to have additional properties beyond ID & word, to allow multi-dimensional indexing. Otherwise we're not going to get the speed boosts we need. For example, for the forum posts search it would need to have forum IDs in there, so that it can have word & forum ID in a single index and easily carve out the result sets needed.
By Guest posted
Further explanation...

1) multi-dimensional indexing. Full text gets very slow because it hits the full text index for a set of matching post IDs but then it has to intersect these against other search parameters, such as forum ID. For a popular term such as 'comcode' this may mean MySQL skipping through 100s of posts that match the term for every 1 it finds in the right forum. With our own custom multi-dimensional index we have those intersections precomputed.

2) background indexing. We can populate the indexing in the background, rather than real-time. Meaning we can have our expansive indexing with no performance hit.

3) custom stop word lists and min-max word lengths. MySQL's list of stop words ('the', 'an', etc) is compiled in, as is the word lengths. With our own implementation we would have much more control. I'm sure there's words not included in searches that we want, and words so common that their presence in queries really bogs down index traversal.

4) searches no longer creating read locks. As searching right now has to do the aforementioned intersecting against post data held in the main f_posts table, it will create read locks (which block writes). We could massively reduce read locking if the main work is happening in isolated indexes rather than the main table.
EDIT: Actually the locks are probably still needed as we do need to query against it, so things like validation checks can happen, so a join can fix a case of a missing record, and generally to avoid having to do complex double querying. However, as searches will be a lot faster the issue of a long read lock will be gone.

0 guests and 0 members have recently viewed this.