Search API full text queries do not use an index when used via views due to utf8mb4 / utf8 mismatch on PRIMARY

Problem/Motivation

(I will upgrade to 8.x-1.20 soon to verify same still exists but I wanted to raise it anyway)

When using views search with an exposed form, with a database of 10s of thousands of articles, the queries are extremely slow. Reproduced on Percona MySQL Server 5.7 and also MariaDB 10.2.

Query example that takes a long time (20 seconds plus for me):

SELECT t.item_id AS item_id, t_2.created AS created, SUM(t.score) AS score
FROM
(SELECT t.item_id AS item_id, t.score AS score, t.word AS word
FROM
search_api_db_default_text t
WHERE (word IN ('news', 'article')) AND (field_name IN ('field_name', 'field_name_2'))) t
LEFT OUTER JOIN search_api_db_default t_2 ON t.item_id = t_2.item_id
GROUP BY t.item_id, t_2.created
ORDER BY t_2.created DESC
LIMIT 25 OFFSET 0;

EXPLAIN:

+------+-------------+-------+-------+---------------+------------+---------+------+-------+---------------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | Extra                                                               |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+---------------------------------------------------------------------+
|    1 | SIMPLE      | t     | range | word_field    | word_field | 848     | NULL |     4 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | t_2   | index | NULL          | _created   | 9       | NULL | 11309 | Using where; Using index; Using join buffer (flat, BNL join)        |
+------+-------------+-------+-------+---------------+------------+---------+------+-------+---------------------------------------------------------------------+

Issue appears to be caused by the following, which makes the item_id column on search_api_db_default and the item_id column on search_api_db_default_text different collations and therefore unable to be used in an index.

https://www.drupal.org/project/search_api/issues/2902907

Changing the item_id column only on the _text table to utf8_general_ci but keeping remainder of table in tact produces this explain for the same query:

+----+-------------+-------+------------+--------+---------------+------------+---------+---------------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key        | key_len | ref           | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------+------------+---------+---------------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range  | word_field    | word_field | 848     | NULL          |    4 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | t_2   | NULL       | eq_ref | PRIMARY       | PRIMARY    | 452     | dev.t.item_id |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+---------------+------------+---------+---------------+------+----------+--------------------------------------------------------+

Steps to reproduce

Create a new view for search and sort by authored on from the data source and show rendered entities.
Search using the view multiple times across multiple tabs and note significant CPU on database and extremely slow queries.
You can also capture the search query and use EXPLAIN to see a clear problem in index selection.

Proposed resolution

Consider whether item_id needs to be utf8mb4 or not - and if not - after table conversion convert this column back. Alternatively, alter the table itself and individual columns except the utf8mb4 instead of a full table conversion.

Remaining tasks

* Investigate if item_id needs to be 4-byte wide (likely not if it isn't in the other tables)
* Convert item_id back to 3 byte after conversion or do more targeted conversion
* Consider whether or not instead of table wide conversions including keys this process should just convert the item_id and specific other fields that need to be done, since it seems some of them won't have any issues being utf8mb4 in other tables too