Feature proposal #7322


Redesign searching SQL

Added by krileon about 5 years ago. Updated 4 months ago.

Target version:
Start date:
29 October 2018
Due date:
% Done:


Estimated time:


Searching is extremely slow on large datasets. This is because LIKE '%SEARCH%' can not be optimized. Use a FULLTEXT index with MATCH AGAINST to significantly speed up searching. A fulltext index will be needed on both title and message.

Additionally hashtags need to be completely redone as these will be impossible to search since fulltext indexes do not index # as characters. Instead keep track of the hashtags attached to an activity entry using a separate database table. This should simply contain id | type | object | hashtag (e.g. 4 | activity | 324 | test) then a simple INNER JOIN can be used to filter the results down very quickly. There's unfortunately no efficient way to fix this B/C wise so existing entries will simply have to be re-saved for this to take affect as it'd be too slow to parse every entry for their hashtags on install.

Actions #1

Updated by krileon about 5 years ago

Note FULLTEXT usage can't be implemented unless CBs minimum MYSQL is bumped to 5.6.

Actions #2

Updated by krileon over 4 years ago

  • Target version changed from 5.0.0 to 6.0.0
Actions #3

Updated by krileon 8 months ago

  • % Done changed from 0 to 80

Hashtags and Mentions are now database stored. Still no free-form searching at this time, but is planned however may not implement due to possible performance issues.

Actions #4

Updated by krileon 8 months ago

  • Status changed from Assigned to Resolved
  • % Done changed from 80 to 100
Actions #5

Updated by krileon 4 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF