How to speed up MySQL query

Question

I am trying to join two tables in a MySQL database, but do not seem to use the primary key in the second table. I’m not sure that I’m not mistaken, or if the primary key cannot be used, how to optimize it even more. The request is currently taking 20 seconds.

Request in progress:

SELECT * FROM journeyPatternTimingLink2 INNER JOIN stops ON stops.atcoCode = journeyPatternTimingLink2.from WHERE journeyPatternId = '113958' 

My table structure is as follows:

 CREATE TABLE IF NOT EXISTS `journeyPatternTimingLink2` ( `journeyPatternTimingLinkId` int(11) NOT NULL AUTO_INCREMENT, `journeyPatternId` int(11) NOT NULL, `from` varchar(15) NOT NULL, `to` varchar(15) NOT NULL, `direction` enum('inbound','outbound') NOT NULL, `runTime` varchar(15) NOT NULL, PRIMARY KEY (`journeyPatternTimingLinkId`), KEY `journeyPatternId` (`journeyPatternId`), KEY `from` (`from`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13652793 ; -- -- Table structure for table `stops` -- CREATE TABLE IF NOT EXISTS `stops` ( `atcoCode` varchar(25) NOT NULL, `longitude` varchar(30) NOT NULL, `latitude` varchar(30) NOT NULL, PRIMARY KEY (`atcoCode`), KEY `location` (`longitude`,`latitude`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

And finally, the screenshot that I run explains the request . Should I see some kind of index used in the table of tables?

Thanks.

Answer 1

Your two fields use different encodings.

Make both tables use UTF8 .

Answer 2

You can also use Indexing in the FROM and TO columns in the corresponding tables to speed up the table.

CREATE INDEX indexFrom on travelPatternTimingLink2 (from);

CREATE INDEX indexAtcoCode at stops (atcoCode);