Project

General

Profile

Actions

Feature proposal #7322

open

Redesign searching SQL

Added by krileon about 3 years ago. Updated over 2 years ago.

Status:
Assigned
Priority:
High
Assignee:
Target version:
Start date:
29 October 2018
Due date:
% Done:

0%

Estimated time:

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.

Actions #1

Updated by krileon about 3 years ago

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

Actions #2

Updated by krileon over 2 years ago

  • Target version changed from 5.0.0 to 6.0.0
Actions

Also available in: Atom PDF