Back again to the table structure because I am unsure how to handle something.
I have the main table, with a junction table for director and another table for actor which has one row for each actor and role and a foreign key for the main table to link them.
I am doing a keyword search, where the keyword can be either title (dvdpedia table), director (director table via the junction table), or actor (actor table). I don't know what people will be looking for, could be any of these three things.
So this works for matching title and director:
SELECT DISTINCT dvdpedia.id
LEFT OUTER JOIN ( SELECT director2title.titleId,
GROUP_CONCAT(director SEPARATOR ', ') AS director
INNER JOIN director2title
ON director.id = director2title.pid
GROUP BY director2title.titleId ) AS d
ON d.titleId = dvdpedia.id
WHERE MATCH(title) AGAINST(:title IN BOOLEAN MODE)
OR d.director LIKE :director";
:title and :director are placeholder variables
However, I'm unsure how to match also actors. Since the actor table does not work through a junction table, I can't do the same trick. I tried doing a
WHERE dvdpedia.id IN (SELECT titleId FROM actor WHERE MATCH(title) AGAINST(:actor IN BOOLEAN MODE))
Which works, but takes an obscene amount of time to run. So I'm hoping there is a much better way to do this.
As always, thanks in advance.