Optimize SQL query

Question

I have a problem optimizing this query:

  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

Now this query uses ^ 20 seconds. How to optimize this?

EXPLAIN gives the following:

  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 

This is an example server with my data:

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

Answer 1

It looks like your db is dead. Getting rid of an internal query is a key part of optimization. Try this (untested) query:

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 

To optimize your query, you can also share the receipt of articles with the latest version from full-text search, since the latter is the most expensive. This can be done by subquery (also not tested on your 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 

Answer 2

The first thing I noticed in your database is that you do not have an index on xv_articleindex . adressinsql . Add it, and this should greatly improve query performance. In addition, one table is MyISAM and the other is InnoDb. Use one engine (in general, I would recommend InnoDB)