Witam.
Mam problem z zapytaniem:
SELECT SQL_CALC_FOUND_ROWS *,
S.`version` as `Version`,
S.`contents` as `Contents`,
S.`idarticle` as `AdressInSQL`,
S.`topic` as `Topic`,
MATCH(S.`topic`,S.`contents`) AGAINST( 'dokumentalne' ) AS `Relevance`,
IA.`url` as `URL`
FROM `xv_article` AS `S`,
`xv_articleindex` AS `IA`
WHERE MATCH(S.`topic`,S.`contents`) AGAINST( 'dokumentalne' ) AND S.`idarticle` = IA.`adressinsql` AND IA.`accepted` = 1
AND S.`version` = (
SELECT MAX(vercheck.`version`)
FROM `xv_article` AS `vercheck`
WHERE vercheck.`idarticle` = S.`idarticle`
) ORDER BY `Relevance` DESC LIMIT 0 , 30;
Na serwerze trwa ^70sekund!
Nie jest to problem z dużą ilością danych , bo jak dam zapytanie:
SELECT
`version` as `Version`,
`contents` as `Contents`,
`idarticle` as `AdressInSQL`,
`topic` as `Topic`,
MATCH(`topic`,`contents`) AGAINST( 'dokumentalne' ) AS `Relevance`
FROM
`xv_article`
WHERE
MATCH(`topic`,`contents`) AGAINST( 'dokumentalne' )
ORDER BY `Relevance`
To trwa ono parę milisekund.
Nie mam już pomysłu jak znów ten problem ugryźć.
Proszę o pomoc.
Dla testów serwer MySQL:
user: bordeux_4prog
hasło: 4prog
phpmyadmin: http://phpmyadmin.bordeux.net/
chive: http://chive.bordeux.net/
Próbowałem samemu to rozwiązać, np użyć inner JOIN ```sql SET @search = "dokumentalne"; SELECT `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` GROUP BY `idarticle`) AS `MG` ON (`AA`.`idarticle`=`MG`.`idarticle`) /* Bez tego zapytanie trwa 2sek */ WHERE MATCH(`AA`.`topic`,`AA`.`contents`) AGAINST( @search ) AND `IA`.`accepted` = "yes" ORDER BY `Relevance` DESC LIMIT 0 , 30; ``` Ale też to trwa długo, i nie uwzględnia warunku MAX(VERSION)
Co na to począć?
//tresc poprawiona