Knowing id is the primary key...
Lets say we have an index in MySQL like
account_id / assignee_id / is_public
but the sorting of a query that needs this index is by id (the primary index)
changing the index to
account_id / assignee_id / is_public / id
is easier on the DB. Correct or incorrect?
If so where do you see that in the docs?
The query has account_id, assignee_id, is_public in the WHERE clause, then ordering by id. But the column to the sort is id is the primary key in the DB and hence does MySQL by default keep the primary key in order.
A query with a WHERE clause like this:
...
WHERE account_id=? AND assignee_id=? AND is_public=?
ORDER BY id
Would benefit from an index defined on columns (account_id, assignee_id, is_public, id).
https://dev.mysql.com/doc/refman/en/innodb-index-types.html says:
In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.
If you define the table using the InnoDB storage engine (which has been the default storage engine in MySQL for years), then the primary key column is implicitly appended to the index. So an index on (account_id, assignee_id, is_public) is actually an index on (account_id, assignee_id, is_public, id) without you specifying it.
This is not necessarily true if you use a different storage engine (you should have a very good reason for doing so).
If any of the conditions in the WHERE clause are not using =, then the rules change. That is, if you use any inequality or range comparisons, then the sorting-by-primary-key optimization of the index doesn't work, and MySQL has to sort the result manually.
Read more about how MySQL optimizes ORDER BY here: https://dev.mysql.com/doc/refman/en/order-by-optimization.html
You should use EXPLAIN to analyze how MySQL plans to use indexes for your query.
You might like my presentation How to Design Indexes, Really, or the video of me presenting it: https://www.youtube.com/watch?v=ELR7-RdU9XU