SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 61
  1. #26
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This query IS faster, but produces one row for each actor in a movie, see attached screenshot.
    Attached Images Attached Images
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    "if you also wanted to collapse the actors into a concatenated value, you would do that in a subquery as well"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'll try it as soon as I get home.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  4. #29
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, things are looking good. Now I have a question about how to handle editions for movies. There is a lot of information that might change between one edition and another, all these fields for example:

    title, features, image, duration, release, rated, sound, language, subtitles, region, media, # of discs, format, aspect ratio, price, etc.

    So I need to put them in another table. My question is, what would be the best way to handle this? It seems that right now, the fields that remain the same are less than the fields that change. Also, the default edition (for lack of a better term) will also need to have an entry in the "edition" for the info stored there.

    So I'll end up with a table that is the main table (dvdpedia) but has little info and an edition table that has most of the info with multiple entries for each title in the main table.

    Does that sound ok? It doesn't "feel" right to me, I can't help but wonder if I'm missing something.

    Here's my query so far:

    Code MySQL:
    SELECT dvdpedia.id
         , dvdpedia.origTitle AS 'Original title'
         , rated.rated
         , w.writers
         , a.cast
         , studios.studio
         , aspectRatio.aspect
         , videoFormat.format
         , l.languages
         , country.country
      FROM dvdpedia 
    LEFT OUTER JOIN edition ON edition.titleId = dvdpedia.id  
    LEFT OUTER JOIN rated ON rated.id = edition.ratedId 
    LEFT OUTER JOIN videoFormat ON edition.videoFormatId = videoFormat.id
    LEFT OUTER JOIN aspectRatio ON  edition.aspectRatioId = aspectRatio.id
    LEFT OUTER JOIN studios ON dvdpedia.studioId = studios.id
    LEFT OUTER JOIN country ON dvdpedia.countryId = country.id
     
    LEFT OUTER JOIN ( SELECT writer2dvd.titleId
                  , GROUP_CONCAT(CONCAT(writers.name, ' ', writers.lastName) SEPARATOR ', ') AS writers
               FROM writer2dvd 
             INNER JOIN writers 
                 ON writers.id = writer2dvd.pId
             GROUP 
                 BY writer2dvd.titleId ) AS w
        ON w.titleId = dvdpedia.id
     
        LEFT OUTER JOIN ( SELECT actor2title.titleId, 
        				GROUP_CONCAT(CONCAT(actors.name, ' ', actors.lastName) SEPARATOR ', ') AS cast 
        			FROM actors 
    	    		INNER JOIN actor2title 
    	    			ON actors.id = actor2title.pId GROUP BY actor2title.titleId) AS a 
        ON a.titleId = dvdpedia.id
     
    LEFT OUTER JOIN (SELECT lang2title.titleId, GROUP_CONCAT(languages.lang SEPARATOR ', ' ) AS languages
    				FROM languages 
    				INNER JOIN  lang2title ON lang2title.pId = languages.id) AS l
    		ON l.titleId = edition.id;
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you forgot the GROUP BY in the third subquery

    also, you don't have any edition columns in the SELECT clause, so the results of the query will surely be confusing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #31
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quite so, thanks! Forgetting the GROUP BY was causing all the languages to show up in the first movie edition only. Not sure why that is, but glad it works.

    So does the whole edition and current query seem ok to you?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    So does the whole edition and current query seem ok to you?
    just looking at the syntax, i think so

    of course, i'm not really all that familiar with your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I may try your patience for a bit, could you tell me why the GROUP BY clause causes the subqueries to work properly. There's always RTFM, but in the case of subqueries I am a bit confused, trying to make sense of all of this
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  9. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    GROUP BY produces one row for every distinct combination of values of the GROUP BY columns

    so if you write GROUP BY foo,bar, you get one row for every unique combination of values of the foo and bar columns

    if you write GROUP BY titleId, you get one row for every titleId

    thus if your subquery accesses a table that has multiple rows per titleId, and you GROUP BY titleId, then the subquery will produce only one row per titleId, which can then be safely joined to the other tables in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #35
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhhhhhh, gotcha. Thanks a lot for the explanation.

    How's the weather in Toronto by the way? I lived there for a year, loved the city, hated the winters
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  11. #36
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    weather in toronto

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #37
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Rudy and all the other SQL gods.

    I have a followup question.

    I want to search by actor, but then also have the title id in the dvdpedia table that contains entries for those actors.

    This is easy, but I want to do it in a way that initially only searches the actors table and then searches the dvdpedia table only if there is a match found in the actors table so as to have a much faster query. But I'm not sure how to do that other than 2 separate queries, the second one with the ID's from the first one, though I'm thinking there has to be a better way no?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  13. #38
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I came up with this query that uses 2 subqueries but of course it's terribly inneficient:

    Code MySQL:
    select id from edition where id in 
       (select titleId from actor2title where pId in 
       (select id from actors where name like '%robert%' or lastName like '%robert') )
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  14. #39
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    ...I want to do it in a way that initially only searches the actors table and then searches the dvdpedia table only if there is a match found in the actors table so as to have a much faster query.
    try this --
    Code:
    SELECT edition.id 
         , edition.title
         , edition.rating
      FROM actors
    INNER
      JOIN actor2title
        ON actor2title.pid = actors.id
    INNER
      JOIN edition
        ON edition. = actor2title.titleId
     WHERE actors.name LIKE '%robert%' 
        OR actors.lastName LIKE '%robert'
    to see how it actually executes, and how the execution resembles your statement closely, run an EXPLAIN on this query, then compare it to the EXPLAIN for your sub-subquery approach

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #40
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, can't really determine that much difference between them with EXPLAIN, other than the fact that my query has 3 lookups for a WHERE clause and yours has one, so I think that makes it 'lighter'.

    What I'm thinking is that it might be better to run the query on the actors table by itself and then only run another query if a match was found, but that might take a littl emore time. Not sure.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  16. #41
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    ... other than the fact that my query has 3 lookups for a WHERE clause and yours has one
    that's a pretty big difference, wouldn't you say?

    can you post both EXPLAINs?

    Quote Originally Posted by pata View Post
    What I'm thinking is that it might be better to run the query on the actors table by itself and then only run another query if a match was found.
    stop trying to outthink the optimizer -- it's ~way~ smarter than you or me

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #42
    SitePoint Zealot cruncher06's Avatar
    Join Date
    Feb 2009
    Location
    Illinois, USA
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think a junction table exactly as stated will do the trick.

    Chris

  18. #43
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the explain based on your query, I had to modify it a bit to get the other data I needed:


    Code MySQL:
    +----+-------------+-------------+--------+---------------+---------+---------+----------------------------+------+---------------------------------------------+
    | id | select_type | table       | type   | possible_keys | key     | key_len | ref                        | rows | Extra                                       |
    +----+-------------+-------------+--------+---------------+---------+---------+----------------------------+------+---------------------------------------------+
    |  1 | SIMPLE      | edition     | ALL    | NULL          | NULL    | NULL    | NULL                       |    5 | Using temporary; Using filesort             |
    |  1 | SIMPLE      | actor2title | index  | PRIMARY       | PRIMARY | 8       | NULL                       |   16 | Using where; Using index; Using join buffer |
    |  1 | SIMPLE      | actors      | eq_ref | PRIMARY       | PRIMARY | 4       | bruji_data.actor2title.pId |    1 | Using where                                 |
    +----+-------------+-------------+--------+---------------+---------+---------+----------------------------+------+---------------------------------------------+

    This is the actual query:

    Code MySQL:
    SELECT edition.id, edition.title, edition.image, CONCAT(actors.name, ' ', actors.lastName) AS cast
    			  FROM actors
    			INNER
    			  JOIN actor2title
    			    ON actor2title.pid = actors.id
    			INNER
    			  JOIN edition
    			    ON edition.titleId = actor2title.titleId
    			 WHERE actors.name LIKE '%$cast%' 
    			    OR actors.lastName LIKE '%$cast' ORDER BY title

    And this is the EXPLAIN from the query I had above (not copying it here again to avoid confusion):


    Code MySQL:
    +----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+--------------------------+
    | id | select_type        | table       | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
    +----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+--------------------------+
    |  1 | PRIMARY            | edition     | index           | NULL          | PRIMARY | 4       | NULL |    5 | Using where; Using index |
    |  2 | DEPENDENT SUBQUERY | actor2title | index           | NULL          | PRIMARY | 8       | NULL |   16 | Using where; Using index |
    |  3 | DEPENDENT SUBQUERY | actors      | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using where              |
    +----+--------------------+-------------+-----------------+---------------+---------+---------+------+------+--------------------------+
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  19. #44
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cruncher06 View Post
    I think a junction table exactly as stated will do the trick.
    I'm sorry Chris, I don't know what you are referring to here.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  20. #45
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    a "junction" table, also called an association table, or a relationship table, or a linking table, or a many-to-many table, is exactly what you already have -- actor2title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #46
    SitePoint Zealot cruncher06's Avatar
    Join Date
    Feb 2009
    Location
    Illinois, USA
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a "junction" table, also called an association table, or a relationship table, or a linking table, or a many-to-many table, is exactly what you already have -- actor2title
    What r937 said what I was meaning.

  22. #47
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    Now I need to do fulltext searches on this database. I've never used fulltext search before and have been reading up on it, but any pointers on how to create the indexes would be very welcome. I am wondering for example if it is possible to create one index that mixes columns from different tables. That sure would be handy.

    Cheers.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  23. #48
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by pata View Post
    I am wondering for example if it is possible to create one index that mixes columns from different tables.
    nope, not possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #49
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Any pointers on how to set the indexes up? Or is it simply set up the columns I want to use fulltext search for and that's it?
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  25. #50
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,214
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's pretty much it

    da manual has several examples

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •