优化SQL查询

我有优化此查询的问题：

SET @SEARCH = "dokumentalne";

SELECT SQL_NO_CACHE
`AA`.`version` AS `Version` , 
`AA`.`contents` AS `Contents` , 
`AA`.`idarticle` AS `AdressInSQL` , 
`AA` .`topic` AS `Topic` ,
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `Relevance` , 
`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 `version`
    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 `Relevance` DESC
LIMIT 0 , 30

现在，这个查询使用^ 20秒。 如何优化这个？

解释给出了这个：

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

这是我的数据的示例服务器：

user: bordeux_4prog
password: 4prog
phpmyadmin: http://phpmyadmin.bordeux.net/
chive: http://chive.bordeux.net/

---

看起来你的数据库已经死了。 摆脱内部查询是优化的关键部分。 请试试这个（未经测试）查询：

SET @SEARCH = "dokumentalne";

SELECT SQL_NO_CACHE
  aa.idarticle AS `AdressInSQL`,
  aa.contents AS `Contents`,
  aa.topic AS `Topic`,
  MATCH(aa.topic , aa.contents) AGAINST (@SEARCH) AS `Relevance`,
  ia.url AS `URL`,
  MAX(aa.version) AS `Version`
FROM
  xv_article AS aa,
  xv_articleindex AS ia
WHERE
  aa.idarticle = ia.adressinsql
  AND ia.accepted = "yes"
  AND MATCH(aa.topic , aa.contents) AGAINST (@SEARCH)
GROUP BY
  aa.idarticle,
  aa.contents,
  `Relevance`,
  ia.url
ORDER BY
  `Relevance` DESC
LIMIT
  0, 30

为了进一步优化您的查询，您可能还会从全文搜索中获取最新版本的文章，因为后者是最昂贵的。 这可以通过subquerying（也没有在你的db上测试过）完成：

SELECT SQL_NO_CACHE
  iq.idarticle AS `AdressInSQL`,
  iq.topic AS `Topic`,
  iq.contents AS `Contents`,
  iq.url AS `URL`,
  MATCH(iq.topic, iq.contents) AGAINST (@SEARCH) AS `Relevance`
FROM (
   SELECT
     a.idarticle,
     a.topic,
     a.contents,
     i.url,
     MAX(a.version) AS version
   FROM
     xv_article AS a,
     xv_articleindex AS i
   WHERE
     i.accepted = "yes"
     AND a.idarticle = i.adressinsql
   GROUP BY
     a.idarticle AS id,
     a.topic,
     a.contents,
     i.url
) AS iq
WHERE
  MATCH(iq.topic, iq.contents) AGAINST (@SEARCH)
ORDER BY
  `Relevance` DESC
LIMIT
  0, 30

---

我在你的数据库中注意到的第一件事是你没有在xv_articleindex上的索引。 adressinsql 。 添加它，它应该显着提高查询性能。 另外，一个表是MyISAM，而另一个表是InnoDb。 使用一个引擎（一般来说，我会推荐InnoDB）

链接地址: http://www.djcxy.com/p/62447.html

上一篇: Optimize SQL Query

下一篇: Jquery autocomplete by categories not returning selected result