Slow loading searches and tags on large data sets

## Feature Request
Allow search and tags to work with large databases and result sets.

**Is your feature request related to a problem? Please describe.**
We have a Flarum DB with 8 million users, 2 million discussions and over 15 million threads. Tags can take up 10 seconds for us to load and search always times out. If I try running the queries Flarum produces directly, the average search takes over 100 seconds to return 21 results in the end.

**Describe the solution you'd like**
Please note that this is just a suggestion and a quick fix that I've come up with given the time constraints I have to go into production.

I believe the general slowness is tied to using search gambits and eloquent events and just in general the way eloquent makes things so flexible, but it all comes at the cost of ugly and inefficient queries when they are assembled in the end. As such, on a big dataset, the tags pages and searches become un-useable so we need a clean way to resolve this. I will paste a git patch here for what I have written to deal with the problem in src/Discussion/Search/DiscussionSearcher.php - but I am not a Laravel or Eloquent developer, so there is almost certainly a better way to fix in this in SQL. Eventually, it would be great to have an Elastic Search add-on, but bad queries probably need to be fixed anyway.

**Justify why this feature belongs in Flarum's core, rather than in a third-party extension**
Because search is a core feature of Flarum.

**Describe alternatives you've considered**
None, but we have thought of other ideas like flattening the data and putting it into a view or using Elastic Search, but we think these should be step 2, once have optimised the way Flarum gets data from the database.

The patch below means that on our database, even keeping the not-so-pretty SQL produced by the whereVisibleTo() method on a model, we can bring back searches and tags in under 1 second. Tags took about 5-10 seconds before and searches over a minute. This code now also works with sticky tag searches and follows, but as you can see from the code, it is quite limiting and essentially a hack.

**Patch:**
```
diff --git a/src/Discussion/Search/DiscussionSearcher.php b/src/Discussion/Search/DiscussionSearcher.php
index d342acfc..e3e41c8b 100644
--- a/src/Discussion/Search/DiscussionSearcher.php
+++ b/src/Discussion/Search/DiscussionSearcher.php
@@ -61,19 +61,54 @@ class DiscussionSearcher
{
$actor = $criteria->actor;

+ // Check what we've got
+ $tagPosition = strpos($criteria->query, ' tag:');
+ if ($tagPosition > 0) {
+ $kw = explode(' tag:', $criteria->query);
+ $keyword = $kw[0];
+ $tag = $kw[1];
+ } else {
+ $tag = str_replace(' tag:', '', $criteria->query);
+ }
+ $isTagSearch = $tagPosition !== false;
+ $isFollowing = strpos($criteria->query, 'is:following');
+ $isByobu = strpos($criteria->query, 'byobu:') !== false;
+ $isAuthor = strpos($criteria->query, 'author:') !== false;
+ $isNormalSearch = !$isByobu && !$isTagSearch && !$isAuthor && !$isFollowing && $criteria->query !== null;
+
$query = $this->discussions->query()->select('discussions.*')->whereVisibleTo($actor);

- // Construct an object which represents this search for discussions.
- // Apply gambits to it, sort, and paging criteria. Also give extensions
- // an opportunity to modify it.
+ if ($isTagSearch) {
+ $query->leftJoin('discussion_tag', 'discussions.id', '=', 'discussion_tag.discussion_id')
+ ->leftJoin('tags', 'discussion_tag.tag_id', '=', 'tags.id')
+ ->where('tags.slug', $tag)
+ ->orderBy('is_sticky', 'DESC');
+ if ($tagPosition > 0) {
+ // Search inside a tag
+ $query->where('discussions.title', 'like', '%' . $keyword . '%');
+ }
+
+ } elseif ($isNormalSearch) {
+ $query->where('id', 'in', function($query) {
+ $query->select('posts.discussion_id')->from('posts')->where('posts.content','like', '%' . $criteria->query . '%')->orderBy('id');
+ })
+ ->orWhere('discussions.title', 'like', '%' . $criteria->query . '%')
+ ->orderBy('discussions.last_posted_at', 'desc');
+ }
+
$search = new DiscussionSearch($query->getQuery(), $actor);

- $this->gambits->apply($search, $criteria->query);
- $this->applySort($search, $criteria->sort);
+ if (!$isNormalSearch) {
+ $this->applySort($search, $criteria->sort);
+ }
+
$this->applyOffset($search, $offset);
$this->applyLimit($search, $limit + 1);

- $this->events->dispatch(new Searching($search, $criteria));
+ if (!$isTagSearch && !$isNormalSearch) {
+ $this->gambits->apply($search, $criteria->query);
+ $this->events->dispatch(new Searching($search, $criteria));
+ }

// Execute the search query and retrieve the results. We get one more
// results than the user asked for, so that we can say if there are more

```
**New Search method in DiscussionSearcher.php:**
```
public function search(SearchCriteria $criteria, $limit = null, $offset = 0)
{
$actor = $criteria->actor;

// Check what we've got
$tagPosition = strpos($criteria->query, ' tag:');
if ($tagPosition > 0) {
$kw = explode(' tag:', $criteria->query);
$keyword = $kw[0];
$tag = $kw[1];
} else {
$tag = str_replace(' tag:', '', $criteria->query);
}
$isTagSearch = $tagPosition !== false;
$isFollowing = strpos($criteria->query, 'is:following');
$isByobu = strpos($criteria->query, 'byobu:') !== false;
$isAuthor = strpos($criteria->query, 'author:') !== false;
$isNormalSearch = !$isByobu && !$isTagSearch && !$isAuthor && !$isFollowing && $criteria->query !== null;

$query = $this->discussions->query()->select('discussions.*')->whereVisibleTo($actor);

if ($isTagSearch) {
$query->leftJoin('discussion_tag', 'discussions.id', '=', 'discussion_tag.discussion_id')
->leftJoin('tags', 'discussion_tag.tag_id', '=', 'tags.id')
->where('tags.slug', $tag)
->orderBy('is_sticky', 'DESC');
if ($tagPosition > 0) {
// Search inside a tag
$query->where('discussions.title', 'like', '%' . $keyword . '%');
}

} elseif ($isNormalSearch) {
$query->where('id', 'in', function($query) {
$query->select('posts.discussion_id')->from('posts')->where('posts.content','like', '%' . $criteria->query . '%')->orderBy('id');
})
->orWhere('discussions.title', 'like', '%' . $criteria->query . '%')
->orderBy('discussions.last_posted_at', 'desc');
}

$search = new DiscussionSearch($query->getQuery(), $actor);

if (!$isNormalSearch) {
$this->applySort($search, $criteria->sort);
}

$this->applyOffset($search, $offset);
$this->applyLimit($search, $limit + 1);

if (!$isTagSearch && !$isNormalSearch) {
$this->gambits->apply($search, $criteria->query);
$this->events->dispatch(new Searching($search, $criteria));
}

// Execute the search query and retrieve the results. We get one more
// results than the user asked for, so that we can say if there are more
// results. If there are, we will get rid of that extra result.
$discussions = $query->get();

$areMoreResults = $limit > 0 && $discussions->count() > $limit;

if ($areMoreResults) {
$discussions->pop();
}

return new SearchResults($discussions, $areMoreResults);
}
```

Start in src/Discussion/Search/DiscussionSearcher.php and trace how SearchCriteria, gambits, Eloquent events, and whereVisibleTo assemble search and tag queries. Reproduce the reported searches and tag loads against a large database, then verify that the resulting implementation avoids timeouts while preserving sticky-tag, follows, and visibility behavior.

Written by the indexing model from the issue text.