使用联接的MySQL选择查询花费的时间过长

Question

我有以下带有表联接的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 ;

Answer 1

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