Speeding up a mySQL JOIN query

I'm trying to see up a query in mySQL. The table structure looks like:

CREATE TABLE `glinks_BuildRelations` (
  `relation_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `link_id` int(11) NOT NULL,
  `page_num` int(11) NOT NULL,
  `distance` float DEFAULT NULL,
  `paid` int(11) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

ALTER TABLE `glinks_BuildRelations`
  MODIFY `relation_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2384882;
COMMIT;

CREATE TABLE `glinks_Link_Descriptions_with_URLs` (
  `description_id` mediumint(8) NOT NULL,
  `Description` text DEFAULT NULL,
  `Description_gite` text DEFAULT NULL,
  `Directions` text DEFAULT NULL,
  `Description_ES` text DEFAULT NULL,
  `Description_gite_ES` text DEFAULT NULL,
  `Directions_ES` text DEFAULT NULL,
  `Description_EN` text DEFAULT NULL,
  `Description_gite_EN` text DEFAULT NULL,
  `Directions_EN` text DEFAULT NULL,
  `Multilang_english_Description` text DEFAULT NULL,
  `Multilang_espanol_Description` text DEFAULT NULL,
  `Short_Description` text DEFAULT NULL,
  `link_id_fk` mediumint(7) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `glinks_Link_Descriptions_with_URLs`
  ADD PRIMARY KEY (`description_id`),
  ADD KEY `link_id_fk` (`link_id_fk`);

ALTER TABLE `glinks_Link_Descriptions_with_URLs`
  MODIFY `description_id` mediumint(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=121050;
COMMIT;

(I tried to make a SQL fiddle with it, but it complained about the index!)

The table holds 121k rows in glinks_Link_Descriptions_with_URLs, and then 2,384,881 rows in the glinks_BuildRelations table. The query I'm using needs to

Grab the records from glinks_BuildRelations , like: SELECT * FROM glinks_BuildRelations as relations WHERE cat_id = 197
Then grab the values from glinks_Link_Descriptions_with_URLs where descs.link_id_fk = relations.link_id match)

This one is fast:

SELECT * FROM glinks_BuildRelations as relations WHERE cat_id = 197; 0.012 seconds

The other one with JOIN is much slower, it seems:

SELECT * FROM glinks_BuildRelations as relations
            JOIN glinks_Link_Descriptions_with_URLs AS descs ON descs.link_id_fk = relations.link_id
            WHERE cat_id = 197
    5720 rows in set (6.928 sec)

Is there a way I can speed this up?

Solution

Here are recommendations for better performance:

Use InnoDB for all tables, not MyISAM. InnoDB has better performance for almost every workload.
Define a primary key for each table. The auto-increment column must be a key.
Don't bother with mediumint for the primary key. MySQL internally uses four bytes anyway, so there's no advantage to using a mediumint unless you want it to stop taking new rows after 8388607. Just use int or bigint.
Don't bother with int(11), just use int. What looks like a length specifier doesn't mean anything, and it is deprecated in MYSQL 8.0.
Make the foreign key column the same data type as the primary key it references in the other table. I.e. INT is not the same as INT UNSIGNED. They must be the same type.
Use utf8mb4 instead of utf8mb3.
Create an index for the column you search on (cat_id), and the column you join (link_id_fk). Creating a foreign key implicitly creates an index if one does not exist.

Here's what I ended up with for definitions:

CREATE TABLE `glinks_BuildRelations` (
  `relation_id` INT NOT NULL AUTO_INCREMENT,
  `cat_id` INT NOT NULL,
  `link_id` INT NOT NULL,
  `page_num` INT NOT NULL,
  `distance` FLOAT DEFAULT NULL,
  `paid` INT NOT NULL DEFAULT 0,
   PRIMARY KEY (relation_id),
   KEY (cat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=2384882;

CREATE TABLE `glinks_Link_Descriptions_with_URLs` (
  `description_id` INT NOT NULL AUTO_INCREMENT,
  `Description` text DEFAULT NULL,
  `Description_gite` text DEFAULT NULL,
  `Directions` text DEFAULT NULL,
  `Description_ES` text DEFAULT NULL,
  `Description_gite_ES` text DEFAULT NULL,
  `Directions_ES` text DEFAULT NULL,
  `Description_EN` text DEFAULT NULL,
  `Description_gite_EN` text DEFAULT NULL,
  `Directions_EN` text DEFAULT NULL,
  `Multilang_english_Description` text DEFAULT NULL,
  `Multilang_espanol_Description` text DEFAULT NULL,
  `Short_Description` text DEFAULT NULL,
  `link_id_fk` INT NOT NULL,
   PRIMARY KEY (description_id),
   FOREIGN KEY (link_id_fk) REFERENCES glinks_BuildRelations(relation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=121050;

Now verify the query makes use of the indexes:

mysql> explain SELECT * FROM glinks_BuildRelations as relations
         JOIN glinks_Link_Descriptions_with_URLs AS descs
         ON descs.link_id_fk = relations.link_id
         WHERE cat_id = 197;
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref                    | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------------+------+----------+-------+
|  1 | SIMPLE      | relations | NULL       | ref  | cat_id        | cat_id     | 4       | const                  |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | descs     | NULL       | ref  | link_id_fk    | link_id_fk | 4       | test.relations.link_id |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------------+------+----------+-------+

The type: ref in the EXPLAIN report shows it is using an index for each table.