I have a table called
- id (PK INT NOT NULL)
- account_id (FK INT NOT NULL)
- key (INT NOT NULL. UNIQUE for given account_id)
- name (VARCHAR NOT NULL. UNIQUE FOR given account_id)
SELECT COALESCE(MAX(key),0)+1 FROM myEntity WHERE accounts_id=123;
You gave a query as an example not requiring
GROUP BY. For the sake of explanation, I'll simplify it as follows.
SELECT MAX(key) FROM myEntity WHERE accounts_id = 123
Why doesn't that query require
GROUP BY? Because you only expect one row in the result set, describing a particular account.
What if you wanted a result set describing all your accounts with one row per account? Then you would use this:
SELECT accounts_id, MAX(key) FROM myEntity GROUP BY accounts_id
See how that goes? You get one row in this result set for each distinct value of
accounts_id. By the way, MySQL's query planner knows that
SELECT accounts_id, MAX(key) FROM myEntity WHERE accounts_id = '123' GROUP BY accounts_id
is equivalent to the same query omitting the
GROUP BY clause.
One more thing to know: If you have a compound index on
(accounts_id, key) in your table, all these queries will be almost miraculously fast because the query planner will satisfy them with a very efficient loose index scan. That's specific to
MIN() aggregate functions. Loose index scans can't bue used for
AVG() or similar functions; those require tight index scans.