mysql - 带连接的MySQL SELECT查询花费的时间太长

我有以下带有表联接的SELECT查询，它大约需要一分钟才能返回6条记录：

SELECT * FROM specimen, topography_index, morphology, specimen_image_lookup, image
WHERE
SUBSTRING(specimen.topography_index, 2, 2) = topography_index.topography_index_code
AND
morphology.morphology_code = specimen.snop_code
AND
specimen_image_lookup.specimen_fk = specimen.specimen_pk
AND
image.image_pk = specimen_image_lookup.image_fk
AND
specimen.topography_index, 2, 2) IN('".implode("','",$system)."')

有什么想法我应该在这里吗？

表结构为：

CREATE TABLE `specimen` (
  `specimen_pk` int(4) NOT NULL AUTO_INCREMENT,
  `number` varchar(20) NOT NULL,
  `unit_number` varchar(10) NOT NULL,
  `topography_index` varchar(5) NOT NULL DEFAULT '',
  `snop_axis` char(1) NOT NULL,
  `snop_code` varchar(4) NOT NULL,
  `example` int(2) NOT NULL,
  `gender` char(1) NOT NULL,
  `age` varchar(3) NOT NULL DEFAULT 'NA',
  `clinical_history` text NOT NULL,
  `specimen` text NOT NULL,
  `macroscopic` text NOT NULL,
  `microscopic` text NOT NULL,
  `conclusion` text NOT NULL,
  `comment` text NOT NULL,
  `room` char(1) NOT NULL,
  `position` varchar(8) NOT NULL,
  `created` datetime NOT NULL,
  `created_by` int(3) NOT NULL,
  `updated` datetime NOT NULL,
  `updated_by` int(3) NOT NULL,
  PRIMARY KEY (`specimen_pk`),
  FULLTEXT KEY `clinical_history` (`clinical_history`),
  FULLTEXT KEY `specimen` (`specimen`),
  FULLTEXT KEY `macroscopic` (`macroscopic`),
  FULLTEXT KEY `microscopic` (`microscopic`),
  FULLTEXT KEY `conclusion` (`conclusion`),
  FULLTEXT KEY `comment` (`comment`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=500 ;

CREATE TABLE `topography_index` (
  `topography_index_pk` int(3) NOT NULL AUTO_INCREMENT,
  `topography_index_code` varchar(2) DEFAULT NULL,
  `topography_index_nomen` text,
  PRIMARY KEY (`topography_index_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=138 ;


CREATE TABLE `specimen_image_lookup` (
  `specimen_image_lookup_pk` int(8) NOT NULL AUTO_INCREMENT,
  `specimen_fk` int(4) NOT NULL,
  `image_fk` int(4) NOT NULL,
  PRIMARY KEY (`specimen_image_lookup_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=141 ;

CREATE TABLE `morphology` (
  `morphology_pk` int(6) NOT NULL AUTO_INCREMENT,
  `morphology_code` varchar(4) NOT NULL,
  `morphology_nomen` varchar(120) NOT NULL,
  PRIMARY KEY (`morphology_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2295 ;

CREATE TABLE `image` (
  `image_pk` int(4) NOT NULL AUTO_INCREMENT,
  `image_title` varchar(80) NOT NULL,
  `image_description` text NOT NULL,
  `image_thumbnail` varchar(100) NOT NULL,
  `image_small` varchar(100) NOT NULL,
  `image_large` varchar(100) NOT NULL,
  `created` datetime NOT NULL,
  `created_by` int(3) NOT NULL,
  `updated` datetime NOT NULL,
  `updated_by` int(3) NOT NULL,
  PRIMARY KEY (`image_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=138 ;

最佳答案

通过对sample.topography_index执行子字符串，您要求数据库在查找topography_index中是否存在值之前对样本表中的每一行执行该计算。解决此问题的一种方法是存储将与topography_index匹配的实际整数值，而不是存储嵌入该值的字符串。

关于mysql - 带连接的MySQL SELECT查询花费的时间太长，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25635540/