[MySQL] Optymalizacja zapytania v2

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

Wkleiłem zapytanie w tego testowego PMA i wynik dostałem w 0.0002s, więc albo miałeś pecha albo po prostu siedzi już w query cache'u.
Swoją drogą masz tam 2x użycie temporary table, 2x filesort.

Brak kluczy dla podzapytania (<code>SELECT idarticle, MAX(version) AS versionFROMxv_articleGROUP BYidarticle</code>) -> "Using index; Using temporary; Using filesort". Po dodaniu indeksu czas wykonania to 1.17s. Kolejne znowu 0.0003 (więc query cache).

Wciąż zostaje filesort i temporary, ale jeden.

OK. Przyczyną tak szybkiej odpowiedzi serwera były złe dane - zmieniłem prawdziwe na losowe, by je ochronić -> z tego powodu szybciej się wyswietlało. Teraz dałem orginalne...

Poprawiłem też treść tematu: bo tamte zapytanie co ja dałem, to nie daje wyniku, którego ja chce.

nie brało pod uwagę wersji ponieważ nie ująłeś tego w warunku.
a co do zapytania to spróbuj:

SELECT 
`AA`.`version` AS `Version` , 
`AA`.`contents` AS `Contents` , 
`AA`.`idarticle` AS `AdressInSQL` , 
`AA`.`topic` AS `Topic` ,
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST ('dokument') 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 ('dokument') 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

pozdrawiam
paweld

Teraz wybiera rekordy właściwie, ale nadal nie umiem uporać się z szybkością. Dziękuje!

jest ona w granicach 24sekund:

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

Zrób może w ten sposób, że utwórz tabele która będzie przetrzymywała idarticle i maksymalną wersje tego artykułu (np. xv_maxv). Dla tabeli xv_articles dodaj trigger, który po każdym insercie będzie dodawał nowy rekord z idarticle i version do xv_maxv jeżeli xv_article.version=1 lub lub uaktualniał version dla danego idarticle jeżeli xv_article.version>1. a w zapytaniu będziesz korzystał wlaśnie z tej tabeli a nie z selecta. Kod z uzyciem tabeli tymczasowej wykonuje się około 2,4 s:

create temporary table xv_maxv (idarticle char(13), version mediumint(5), index idxia (idarticle));
insert into xv_maxv
SELECT `idarticle` , MAX( `version` ) AS `version` FROM `xv_article` GROUP BY `idarticle`;

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 `xv_maxv` AS `MG` ON ( `AA`.`idarticle` = `MG`.`idarticle` )
WHERE
MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH)
AND `IA`.`accepted` = "yes"
AND `AA`.`version` = `MG`.`version`
ORDER BY `Relevance` DESC
LIMIT 0 , 30

pozdrawiam
paweld

Myślę że dodanie do tabeli indexarticle Pola z aktualną wersją oszczędzi mi sporo kłopotów z późniejszym rozbudowaniem serwisu.
Dziekuje za super odpowiedzi.
Pozdrowienia