subquery LIMIT alternatives

[cranial-bore]

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

[r937]

3. is an option in this case

can't say more without seeing your query

[cranial-bore]

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:

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?

[r937]

"I need to LIMIT by the parent rows"

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

which one is the parent?

[cranial-bore]

matches. The matches are the main subject of the query

[r937]

the first solution uses LIMIT in a subquery

this doesn't work in all versions of mysql, though

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

[cranial-bore]

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

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
)