[MySQL] Optimize Query
- by bordeux
Hello.
I have problem with optimize 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 using ^ 20 seconds. How to optimize this?
EXPLAIN gives this:
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 example server with my data:
user: bordeux_4prog
password: 4prog
phpmyadmin: http://phpmyadmin.bordeux.net/
chive: http://chive.bordeux.net/
