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)
SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free') ORDER BY record_num DESC LIMIT 30
0.0040 seconds
-----------------------------------------------------------------------------------------------------------------------------------------------
SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free video') ORDER BY record_num DESC LIMIT 30
0.8115 seconds
-----------------------------------------------------------------------------------------------------------------------------------------------
SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen') ORDER BY record_num DESC LIMIT 30
3.7338 seconds
-----------------------------------------------------------------------------------------------------------------------------------------------
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"
SELECT record_num FROM content WHERE MATCH (title,keywords) AGAINST ('free teen young') LIMIT 30
0.5060 seconds
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
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