mysql - 优化sql查询，加快搜索速度，目前搜索时间约为85秒

Question

我有一个数据库，其中有大约 270 万条记录。我需要从中获取记录，因为我正在使用下面的查询

结果

SELECT r3.original_image_title,r3.uuid,r3.original_image_URL FROM `image_attributes` AS r1 INNER JOIN `filenames` as r3 WHERE r1.`uuid` = r3.`uuid` and r3.`status` = 1 and r1.status=1 and (r1.`attribute_name` like "Quvenzhané Wallis%" or r3.original_image_URL like "Quvenzhané Wallis%") group by r3.`uuid` limit 0,20

总计数

SELECT count(DISTINCT(r1.`uuid`)) as count FROM `image_attributes` AS r1 INNER JOIN `filenames` as r3 WHERE r1.`uuid` = r3.`uuid` and r3.`status` = 1 and r1.status=1 and (r1.`attribute_name` like "Quvenzhané Wallis%" or r3.original_image_URL like "Quvenzhané Wallis%")  

表结构如下

CREATE TABLE IF NOT EXISTS `image_attributes` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_name` text NOT NULL,
  `attribute_type` varchar(255) NOT NULL,
  `uuid` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`index`),
  KEY `attribute_type` (`attribute_type`),
  KEY `uuid` (`uuid`),
  KEY `status` (`status`),
  KEY `attribute_name` (`attribute_name`(50))
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2730431 ;



CREATE TABLE IF NOT EXISTS `filenames` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `original_image_title` text NOT NULL,
  `original_image_URL` text NOT NULL,
  `uuid` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`index`),
  KEY `uuid` (`uuid`),
  KEY `original_image_URL` (`original_image_URL`(50))
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=591967 ;

请建议我如何优化查询以使搜索结果更快

Answer 1

我向您推荐一本书，名为'High Performance MySql' 。有一个名为“优化数据库和查询”或类似内容的部分。