如何加快MySQL查询速度

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.

我正在尝试在MySQL数据库中连接两个表,但它似乎没有在第二个表上使用主键。我不确定我是否查询错误或者主键是否无法使用,如何进一步优化它。目前查询运行需要20秒。

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.

2 个解决方案

#1

Your two fields use different charsets.

您的两个字段使用不同的字符集。

Make both tables use UTF8.

使两个表都使用UTF8。

#2

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

您还可以在各个表的FROM和TO列上使用“索引”来加速查找表。

CREATE INDEX indexFrom on journeyPatternTimingLink2(from);

在journeyPatternTimingLink2(from)上创建INDEX indexFrom;

CREATE INDEX indexAtcoCode on stops(atcoCode);

停止时创建INDEX indexAtcoCode(atcoCode);

#1