I might have asked this before (if I did I can’t find it) but is there a way to use fulltext search on a derived table or a better way to write the following query:
SELECT DISTINCT dvdpedia.id, dvdpedia.title, dvdpedia.locale, dvdpedia.localeLanguage, dvdpedia.originalTitle, media.media
, a.actor
, d.director
FROM dvdpedia
LEFT OUTER JOIN ( SELECT director2title.titleId,
GROUP_CONCAT(director SEPARATOR ', ') AS director
FROM director
INNER JOIN director2title
ON director.id = director2title.pid
GROUP BY director2title.titleId ) AS d
ON d.titleId = dvdpedia.id
LEFT OUTER JOIN actor2role ON actor2role.titleId = dvdpedia.id
LEFT OUTER JOIN media ON media.id = dvdpedia.mediaId
INNER JOIN (SELECT actor.id,
GROUP_CONCAT(actor SEPARATOR ', ') AS actor
FROM actor
INNER JOIN actor2role
ON actor.id = actor2role.actorId
GROUP BY actor.id) AS a
ON a.id = actor2role.actorId
WHERE MATCH(dvdpedia.title) AGAINST("pirates" IN BOOLEAN MODE)
OR MATCH(a.actor) AGAINST("pirates" IN BOOLEAN MODE)
OR MATCH(d.director) AGAINST("pirates" IN BOOLEAN MODE)
GROUP BY dvdpedia.title
Basically I need to do a keyword search, hence the ORs there. I don’t know if the search will be for title or actor or director. So I’m joining the tables, but mysql complains that a.actor does not support fulltext indexes. If I put the matches inside the SELECT statements where I get the actor and director it won’t be an OR operation and will immediately fail if it doesn’t find a match.
“a lot faster” is dubious to begin with if you do not have the means to test the difference
let me ask you how much more difficult it would be to construct three different queries based on whether you are searching in the title or actor or director column – this will force you to think about what it is you are actually searching for (i.e. which columns are in your SELECT clause)
that’s the thing, it’s a keyword search so we search in all three (title, actor and director) that’s why I constructed it this way. So three different queries wouldn’t solve the issue cause I need to search in all three.
I’m sorry, give what a try? A separate queries for each? I already have that.
SELECT title, dvdpedia.locale, dvdpedia.localeLanguage, id
, MATCH(title) AGAINST(:title1 IN BOOLEAN MODE) AS relevance
, MATCH(originalTitle) AGAINST(:originalTitle IN BOOLEAN MODE) AS relevance2
FROM dvdpedia
WHERE MATCH(title) AGAINST(:title2 IN BOOLEAN MODE)
OR MATCH(originalTitle) AGAINST(:originalTitle2 IN BOOLEAN MODE)
The :var_name are just placeholders for PDO variables.
But I fail to see how this helps me with the issue of searching 3 tables at the same time.
A UNION query is what I have right now but I’m not happy with it at all. Every time I need to change something it’s quite complex. I will post it when I get home and you can tell me what you think.
SELECT DISTINCT dvdpedia.id, dvdpedia.title, dvdpedia.originalTitle, media.media, locale, localeLanguage, releaseDate, MATCH(title) AGAINST(:relevance IN BOOLEAN MODE) AS relevance
FROM dvdpedia
LEFT OUTER JOIN media ON media.id = dvdpedia.mediaId
WHERE MATCH(title) AGAINST(:title IN BOOLEAN MODE) OR MATCH(originalTitle) AGAINST(:originalTitle IN BOOLEAN MODE)
UNION
SELECT DISTINCT dvdpedia.id, dvdpedia.title, dvdpedia.originalTitle, media.media, locale, localeLanguage, releaseDate, MATCH(director) AGAINST(:relevance2 IN BOOLEAN MODE) AS relevance
FROM director
INNER JOIN director2title ON director.id = director2title.pId
INNER JOIN dvdpedia ON director2title.titleId = dvdpedia.id
LEFT OUTER JOIN media ON media.id = dvdpedia.mediaId
WHERE MATCH(director) AGAINST(:director IN BOOLEAN MODE)
UNION
SELECT dvdpedia.id, dvdpedia.title, dvdpedia.originalTitle, media.media, dvdpedia.locale, dvdpedia.localeLanguage, dvdpedia.releaseDate, MATCH(actor) AGAINST(:actor IN BOOLEAN MODE) AS relevance
FROM actor
INNER JOIN actor2role
ON actor2role.actorId = actor.id
INNER JOIN dvdpedia
ON dvdpedia.id = actor2role.titleId
LEFT OUTER JOIN media ON media.id = dvdpedia.mediaId
WHERE MATCH(actor) AGAINST(:actor2 IN BOOLEAN MODE)
As always Rudy, your help is much appreciated. I’m hoping there is a way to make this query better because right now I’m getting weird results when ordering based on relevance.
