Most efficient way to LIMIT results in a JOIN?
Question by johnnietheblack
I have a fairly simple one-to-many type join in a MySQL query. In this case, I’d like to LIMIT my results by the left table.
For example, let’s say I have an accounts table and a comments table, and I’d like to pull 100 rows from accounts and all the associated comments rows for each.
Thy only way I can think to do this is with a sub-select in in the FROM clause instead of simply selecting FROM accounts. Here is my current idea:
SELECT a.*, c.* FROM
(SELECT * FROM accounts LIMIT 100) a
LEFT JOIN `comments` c on c.account_id = a.id
ORDER BY a.id
However, whenever I need to do a sub-select of some sort, my intermediate level SQL knowledge feels like it’s doing something wrong.
Is there a more efficient, or faster, way to do this, or is this pretty good?
By the way…
This might be the absolute simplest way to do this, which I’m okay with as an answer. I’m simply trying to figure out if there IS another way to do this that could potentially compete with the above statement in terms of speed.
Answer by Priyank
Looks perfect to me.
Just wondering if it is ok with you to receive less or more than 100 rows from the above query.
Because if for a particular row of accounts table there are no rows in comments table then you would get less than 100 rows. Similarly if there are multiple matching rows in comments table for a row in accounts table then you may get more than 100 rows.
See: How can I optimize this query, takes more than a min to execute
Answer by Starx
No, The query is fine, just the way it is. You might want to filter the fields instead of a.* and c.* though.
