[ Golja @ 07.04.2015. 00:04 ] @
tabela ima oko 2 miliona redova, sve je ok kada se pretrazuje jedna rec.. ali ako se pretrazuju 2-3-4 reci onda postaje uzasno spor upit.. takodje ako se doda ORDER BY onda je jos sporiji, iako je sort po primary/index polju fulltext(title,keywords) primary index(record_num) Code: SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free') ORDER BY record_num DESC LIMIT 30 0.0040 seconds ----------------------------------------------------------------------------------------------------------------------------------------------- Code: SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free video') ORDER BY record_num DESC LIMIT 30 0.8115 seconds ----------------------------------------------------------------------------------------------------------------------------------------------- Code: SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen') ORDER BY record_num DESC LIMIT 30 3.7338 seconds ----------------------------------------------------------------------------------------------------------------------------------------------- Code: SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen young') ORDER BY record_num DESC LIMIT 30 4.6115 seconds BEZ ORDER BY brze 4-5 puta, i nema "using filesort" Code: SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen young') LIMIT 30 0.5060 seconds Code: explain SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen young') ORDER BY record_num DESC LIMIT 30 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE content fulltext title_2 title_2 0 1 Using where; Using filesort Code: explain SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen young') LIMIT 30 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE content fulltext title_2 title_2 0 1 Using where svasta sam probao, skroz dropovao indexe, pa kreirao nove, check/optimize table, force index, "match in boolean mode" i slicno, ali nista ne pomaze, i vise izgubih zivce :) da li neko zna da me uputi gde gresim ili sta bih jos mogao da probam ? edit: mysql je 5.5.42, probano je jos na 2 razlicita servera sa 5.3 i mariadb5 |