View Issue Details

IDProjectCategoryView StatusLast Update
0003288Composrcore_database_driverspublic2019-07-31 19:25
ReporterChris GrahamAssigned To 
Status non-assignedResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary0003288: Major overhaul of fulltext search support
DescriptionWe can implement support for fulltext search on all database drivers, regardless of whether the database backends support it.

This is doable via an 'inverted index'. Essentially we just do our own word tokenisation and store it in our own table.

An advantage to this is we can then do much more sophisticated metrics to stop slow searches. We could have fields in our inverted index table for all search qualifiers (post time, author, etc), so that we know we can easily only ever be looking at a subset of the index. Currently the problem with MySQL fulltext search is that the index can not be done against search qualifiers, so you get this huge trawl through non-qualified results as it gets to the occasional qualified ones.

If we do this, I see no reason we can't just drop the regular fulltext search support we have. We can do better, so there's no need to do anything native to each database backend.
Additional InformationAlternatively we can also implement more fulltext search support for individual databases.
DB2, Oracle, and Sqlite (with non-default extension), also support fulltext search but we don't implement it.
If we do this we will need to totally overhaul our fulltext search code too because our code currently assumes it works via indexes, which is not a universally valid assumption. Particularly when indexes are deleted, it doesn't even go into the database driver API for that.

I prefer to implement our own fulltext rather than make a complex multi-vendor API, with lots of testing. It'd be a better result for less investment.
TagsType: Performance
Attach Tags
Time estimation (hours)32
Sponsorship open


related to 0001479 non-assigned Implement sphinx 
related to 0003848 non-assigned Metaphonic search indexing 
related to 0001573 non-assigned Database natural sorting 


There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2017-05-16 22:47 Chris Graham New Issue
2017-05-16 22:47 Chris Graham Tag Attached: Type: Performance
2017-05-16 23:16 Chris Graham Additional Information Updated View Revisions
2017-06-23 02:41 Chris Graham Relationship added related to 0001479
2017-07-05 13:13 Chris Graham Tag Attached: ocProducts client-work (likely)
2018-02-07 20:29 Chris Graham Tag Detached: ocProducts client-work (likely)
2019-07-19 01:45 Chris Graham Relationship added related to 0003848
2019-07-31 19:25 Chris Graham Relationship added related to 0001573