SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    fulltext search on derived tables

    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:

    Code MySQL:
     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?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    ... 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

    Quote Originally Posted by pata View Post
    I don't know if the search will be for title or actor or director.
    why can't you use LIKE?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my understanding is that fulltext searches are a lot faster than LIKE, and also more accurate. Is this not so?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    "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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, give what a try? A separate queries for each? I already have that.

    Code MySQL:
    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.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  10. #10
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the union query I have:


    Code MySQL:
    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)
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks -- if no one else gets to it, i'll have a look tomorrow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  13. #13
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •