baza je mysql, a query je:
Kôd:
$sql = "(
SELECT ID, Title, SUBSTRING(Body,1,180) AS tekst, db_num, MATCH (Title,Body) AGAINST ('$q') AS relevance
FROM articles
WHERE
Title LIKE '%$subdomain%' AND
MATCH (Title,Body) AGAINST ('$q')
LIMIT ".NUM_RESULTS_ARTICLES."
)
UNION
(
SELECT ID, Title, SUBSTRING(Body,1,180) AS tekst, db_num, MATCH (Title,Body) AGAINST ('$q') AS relevance
FROM articles2
WHERE
Title LIKE '%$subdomain%' AND
MATCH (Title,Body) AGAINST ('$q')
LIMIT ".NUM_RESULTS_ARTICLES."
)
UNION
(
SELECT ID, Title, SUBSTRING(Body,1,180) AS tekst, db_num, MATCH (Title,Body) AGAINST ('$q') AS relevance
FROM articles3
WHERE
Title LIKE '%$subdomain%' AND
MATCH (Title,Body) AGAINST ('$q')
LIMIT ".NUM_RESULTS_ARTICLES."
)
ORDER BY relevance DESC
LIMIT ".NUM_RESULTS_ARTICLES;"
poenta je da postoje tri velike tabele(~1GB svaka) sa textovima, i da treba naci textove, ali uz uslov da se $subdomain obavezno pojavljuje u naslovu texta (subdomen je isto neki keyword, da vas ne zbunjuje). Body je tipa text, a Title je varchar(255)
E sad, kad se radi samo fulltext search to radi jako lepo i brzo, ali kad sam dodao ovaj dodatni uslov za Title server je poceo da se vuce stravicno. To mi je extra cudno jer EXPLAIN kaze da se u oba slucaja koristi isti FULLTEXT index, a LIKE ionako ne moze da koristi index, znaci samo treba da isfiltrira dodatno Title polje. Medjutim, nesto se tu mysql-u ne doapada...
Da li postoji neki bolji nacin da se ovo odradi?