SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    subquery LIMIT alternatives

    I have a query with an INNER JOIN, (no subquery) and I need to LIMIT by the parent rows. They get repeated with each of the joined child rows (1:M relationship). Because the LIMIT clause affects the whole result set I am skipping the LIMIT clause and implementing it in PHP instead (breaking the loop when the desired number of parent rows have been reached). I imagine this is a bit wasteful, as some of the result set would be ignored after selecting it.

    I tried to rewrite my query to use a subquery but I discovered MySQL doesn't support LIMIT on a subquery.

    I was thinking of just executing the subquery separately (with a LIMIT) and dropping the result into the main query to effectively get a subquery with a LIMIT, but having to execute two queries.

    So my question - in general terms is it better to:

    1. Execute two queries and have no wasted result records, or
    2. Execute a single query but potentially return a lot more rows than I need
    3. Do something else

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    3. is an option in this case

    can't say more without seeing your query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query is about selecting squash match results. Matches (m) have between 3 and 5 games (g). A match belongs to one season (s), which belongs to one event (e). A match is related to exactly one opponent (o)

    My original query looked like this:
    Code SQL:
    SELECT
        m.ID AS matchID, m.datePlayed, m.win,
        o.ID AS opponentID, o.name AS opponent,
        s.ID AS seasonID, s.name AS season,
        e.ID AS eventID, e.name AS event,
        g.score, g.opponentScore, g.win AS gameWin
    FROM 
        `matches` m, `opponents` o, `events` s, `events` e, `games` g
    WHERE 
        g.matchID = m.ID AND 
        m.eventID = s.ID AND 
        s.parentID = e.ID AND 
        m.opponentID = o.ID AND 
        e.userID = 123 AND
        e.ID = 201 AND              -- variable, not always included in query
        e.ID NOT IN (150, 151) AND  -- variable
        s.ID IN (3, 4) AND          -- variable
        s.ID NOT IN (5, 6) AND      -- variable
        o.ID IN (124, 125) AND      -- variable
        o.ID NOT IN (126, 127) AND  -- variable
        m.datePlayed BETWEEN '2009-01-01' AND '2009-02-26'  -- variable
     
    ORDER BY m.datePlayed DESC

    Yeah, sorry I am using the old style joins you warned against in your article.
    A lot of the WHERE clauses are optional, the query will respond to an advanced search interface so they won't all necessarily be present, but this is an example of a full query with all options.

    So if I want to limit the result to the 10 most recent matches I had been doing that in PHP.
    I hope that clarifies?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    "I need to LIMIT by the parent rows"

    FROM `matches` m, `opponents` o, `events` s, `events` e, `games` g

    which one is the parent?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    matches. The matches are the main subject of the query

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the first solution uses LIMIT in a subquery

    this doesn't work in all versions of mysql, though
    Code:
    SELECT m.ID AS matchID
         , m.datePlayed
         , m.win
         , o.ID AS opponentID
         , o.name AS opponent
         , s.ID AS seasonID
         , s.name AS season
         , e.ID AS eventID
         , e.name AS event
         , g.score
         , g.opponentScore
         , g.win AS gameWin
      FROM ( SELECT *
               FROM matches
              WHERE datePlayed BETWEEN '2009-01-01' 
                                   AND '2009-02-26'
             ORDER
                 BY datePlayed DESC LIMIT 10
           ) AS m
    INNER
      JOIN opponents AS o
        ON o.ID = m.opponentID 
    INNER
      JOIN events AS s
        ON s.ID = m.eventID 
    INNER
      JOIN events AS e
        ON e.ID = s.parentID
    INNER
      JOIN games AS g
        ON g.matchID = m.ID 
     WHERE e.userID = 123 
       AND e.ID = 201 
       AND e.ID NOT IN (150,151) 
       AND s.ID IN (3,4) 
       AND s.ID NOT IN (5,6) 
       AND o.ID IN (124,125) 
       AND o.ID NOT IN (126,127) 
    ORDER 
        BY m.datePlayed DESC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, that did work (MySQL 5.0.51).
    I'm sure I read in the MySQL documentation that LIMIT wasn't supported on sub queries, and I thought that was true even for MySQL 5.1. I must have misunderstood when LIMIT won't work.

    I know my first attempt didn't work, it was in this general format (simplified):
    Code SQL:
    SELECT m.*, o.name AS opponent 
    FROM matches m 
    INNER JOIN opponents o ON m.opponentID = o.ID
    WHERE m.ID IN (
        SELECT ID FROM matches WHERE datePlayed > '2009-01-01' 
        LIMIT 10
    )


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
  •