fulltext search on derived tables

[pata]

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.

Any ideas?

[r937]

... is there a way to use fulltext search on a derived table or a better way to write the following query:

my vote is for the latter -- a better way to write the query

I don't know if the search will be for title or actor or director.

why can't you use LIKE?

[pata]

my understanding is that fulltext searches are a lot faster than LIKE, and also more accurate. Is this not so?

[r937]

"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)

[pata]

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.

[r937]

So three different queries wouldn't solve the issue cause I need to search in all three.

please humour me and give it a try on just one of the three

[pata]

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.

[r937]

But I fail to see how this helps me with the issue of searching 3 tables at the same time.

well, my idea was to build up a UNION query from the three individual queries, but if you don't want to, that's okay too



i notice that you have a new condition (on originalTitle) that wasn't present in your big join query in post #1

[pata]

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.

Thanks.

[pata]

Here's the union query I have:

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)

[r937]

thanks -- if no one else gets to it, i'll have a look tomorrow

[pata]

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.

[pata]

after more testing I found out that the union is actually a lot faster than the join query. I would have thought it was the other way around!