Plugin queries extremely slow

[chuckadams]

This request can take >15s to respond:

http://api.aspirecloud.net/plugins/info/1.2/?action=query_plugins&request%5Bpage%5D=1&request%5Bper_page%5D=36&request%5Blocale%5D=en_US&request%5Bsearch%5D=security&request%5Bwp_version%5D=6.7

Doesn't appear to be any particular params, as this one took 21s to respond as well:

GET https://api.aspirecloud.net/plugins/info/1.2/?action=query_plugins&search=z

However, searching for 'a' returns quickly, so obviously an index isn't being used and we're doing a full table scan.

[chuckadams]

In the 'duh' department:

    private static function applySearch(Builder $query, string $search): void
    {
        $query->where(function (Builder $q) use ($search) {
            $q->where('slug', 'like', "%{$search}%")
                ->orWhere('name', 'like', "%{$search}%")
                ->orWhere('short_description', 'like', "%{$search}%")
                ->orWhereFullText('description', $search);
        });
    }

Those first three are never going to use index. We need something like trigram indexes, and to rewrite the queries to use them.

[chuckadams]

Other bad news for this primitive search implementation is that queries of multiple words like "gravity captcha" are not going to do the right thing, so we'll need to tokenize the search as independent terms. We should also just use fulltext on every column, which might tokenize the search for us anyway.

h/t to @namithj for the initial report and these followups.

[chuckadams]

The main problem was that we weren't using indexes at all -- I simply forgot to add most of them when creating the tables! That's corrected in the above PR, but if we want <100ms searches in the future, we're going to need ElasticSearch or something like it.