Feature proposal #7322
closedRedesign searching SQL
Description
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.
Updated by krileon almost 6 years ago
Note FULLTEXT usage can't be implemented unless CBs minimum MYSQL is bumped to 5.6.
Updated by krileon over 1 year 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.
Updated by krileon over 1 year ago
- Status changed from Assigned to Resolved
- % Done changed from 80 to 100