我有一个优化这个查询的问题:
SET @SEARCH = "dokumentalne";
SELECT SQL_NO_CACHE `AA`.`version` AS `版本`, `AA`.`contents` AS `内容`, `AA`.`idarticle` AS `SQL地址`, `AA` .`topic` AS `主题`, MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `相关度`, `IA`.`url` AS `URL` FROM `xv_article` AS `AA` INNER JOIN `xv_articleindex` AS `IA` ON ( `AA`.`idarticle` = `IA`.`adressinsql` ) INNER JOIN ( SELECT `idarticle` , MAX( `version` ) AS `版本` FROM `xv_article` WHERE MATCH (`topic` , `contents`) AGAINST (@SEARCH) GROUP BY `idarticle` ) AS `MG` ON ( `AA`.`idarticle` = `MG`.`idarticle` ) WHERE `IA`.`accepted` = "yes" AND `AA`.`version` = `MG`.`version` ORDER BY `相关度` DESC LIMIT 0 , 30
现在,这个查询需要 ^ 20 秒。如何进行优化?
EXPLAIN 的结果如下:
1 PRIMARY AA ALL NULL NULL NULL NULL 11169 Using temporary; Using filesort 1 PRIMARY ALL NULL NULL NULL NULL 681 Using where 1 PRIMARY IA ALL accepted NULL NULL NULL 11967 Using where 2 DERIVED xv_article fulltext topic topic 0 1 Using where; Using temporary; Using filesort
这是我的数据示例服务器:
用户:bordeux_4prog 密码:4prog phpMyAdmin:http://phpmyadmin.bordeux.net/ Chive:http://chive.bordeux.net/