How to speed up MySQL query

Question

I'm trying to join two tables in a MySQL database but it doesn't seem to be using the primary key on the second table. I'm not sure if I'm querying it wrong or if the primary key can't be used, how to further optimize it. Currently the query is taking 20 seconds to run.

The query I'm running is:

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 here's a screenshot me running explain on the query. Shouldn't I be seeing some kind of index being used on the stops table?

Thanks.

Answer 1

You may also use "Indexing" on FROM and TO columns on respective tables to speedup table look-up.

CREATE INDEX indexFrom on journeyPatternTimingLink2(from);

CREATE INDEX indexAtcoCode on stops(atcoCode);

Answer 2

Your two fields use different charsets.

Make both tables use UTF8.