Re: Scaling up mysql

[Rick James]

There are many issues, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

100M records is at the 97th percentile of tables asked about in these forums.

Off hand, it sounds like key_buffer_size is too low:
http://mysql.rjweb.org/doc.php/memory

Are you using "IN BOOLEAN MODE"?

Can you get the ids from MATCH, then JOIN to get the rest of the info?