Full-Text Search on different tables (with joins)
Hello guys,
i hope you understand my bad english and can help me with my very big problem.
I have a Fulltextquery like this:
------------------------------------
SELECT m.id FROM medias as m WHERE
MATCH (m.title_search, m.subtitle_search, m.totaltitle_search, m.preferred_title_search, m.uniform_title_search)
AGAINST ("+Evenge* +Bibe*" IN BOOLEAN MODE)
ORDER BY m.title_sort ASC LIMIT 0,50
------------------------------------
He work very performant on my table (most time under 100ms / 500.000 rows)
He looks after Medias with the given title on the media table.
Now i need a more complex search, wich give me the medias for given title OR given Person OR given Tag OR Title
I have tables wich link "authors-id to media-id", "partakes-id to media-id", "tags-id to media-id." (media_*) || Authors/Tag/Partakes can have more then one media.
I have tables wich hold the searchable names. (the could be more then one row per person/tags/bodies... they can have different names 4 example) (persons|bodies|tags_names)
I think it must be a query like this (foreach search-word (exploded by the whitespace) one AND in where)
but this query is of course horrible slow (there are keys on each table, each table have around 500.000 rows)
how can i do it better?
------------------------------------
SELECT m.id FROM medias as m
LEFT JOIN media_authors as ma ON ma.media_id = m.id
LEFT JOIN media_bodies as mb ON mb.media_id = m.id
LEFT JOIN media_partakes as mp ON mp.media_id = m.id
LEFT JOIN media_tags as mt ON mt.media_id = m.id
LEFT JOIN persons_names as pn ON pn.id = ma.person_id OR pn.id = mp.person_id
LEFT JOIN persons_names as pn2 ON pn2.id = mp.person_id
LEFT JOIN bodies_names as bn ON bn.id = mb.bodies_id
LEFT JOIN tags_names as tn ON tn.id = mt.tag_id
WHERE
(MATCH (m.title_search, m.subtitle_search, m.totaltitle_search, m.preferred_title_search, m.uniform_title_search) AGAINST ("+Evenge*" IN BOOLEAN MODE)
OR MATCH (tn.name_search) AGAINST ("+Evenge*" IN BOOLEAN MODE)
OR MATCH (bn.name_search) AGAINST ("+Evenge*" IN BOOLEAN MODE)
OR MATCH (pn.name_search) AGAINST ("+Evenge*" IN BOOLEAN MODE)
OR MATCH (pn2.name_search) AGAINST ("+Evenge*" IN BOOLEAN MODE)
)
AND
(MATCH (m.title_search, m.subtitle_search, m.totaltitle_search, m.preferred_title_search, m.uniform_title_search) AGAINST ("+Bibe*" IN BOOLEAN MODE)
OR MATCH (tn.name_search) AGAINST ("+Bibe*" IN BOOLEAN MODE)
OR MATCH (bn.name_search) AGAINST ("+Bibe*" IN BOOLEAN MODE)
OR MATCH (pn.name_search) AGAINST ("+Bibe*" IN BOOLEAN MODE)
OR MATCH (pn2.name_search) AGAINST ("+Bibe*" IN BOOLEAN MODE)
)
GROUP BY m.id
ORDER BY m.title_sort ASC LIMIT 0,50
------------------------------------
it should be a time under one second
Edited 1 time(s). Last edit at 08/05/2012 10:20AM by Dominik Meyer.