It really depends on the type of query you're making.
If you're talking about a tree, here's how to get a whole sub-tree (as in all descendants of a parent), without fetching parents multiple times, without multiple queries, sorted in a previously defined order and also indexed and really fast: http://dev.mysql.com/tech-resources/articles/hierarchical-da...
When you get the parent data you want a sort order
Not necessarily. People use a sort order mostly to limit the number of rows returned (say you want only the first 50 items with the lowest prices). But optimizing a SELECT using ORDER BY is really hard as there are other restrictions you need to be aware about (like if you're using an index on multiple columns, you can't have a range condition on the first column and sort on the second, at least in MySql).
That's why, if performance is an issue, there are ways to workaround the need to sort -- for example you can keep extra data, like page=1 if position is between 0 and 50, page=2 if position is between 50 and 100, and so on, such that LIMITing the query to the first 50 items is WHERE page=1 (basically storage-efficient precached queries - if the conditions are stable, you can do it).
And in cases where you can't fetch the data efficiently in a single query, you're probably doing it wrong (like you chose the wrong data representation - for example the relational model is really awful for describing anything related to graphs).
Of course, I'm not talking about cases when you're fetching unrelated data or cases where performance doesn't matter or cases when you've got BLOBs in your parent :)
If you're talking about a tree, here's how to get a whole sub-tree (as in all descendants of a parent), without fetching parents multiple times, without multiple queries, sorted in a previously defined order and also indexed and really fast: http://dev.mysql.com/tech-resources/articles/hierarchical-da...
Not necessarily. People use a sort order mostly to limit the number of rows returned (say you want only the first 50 items with the lowest prices). But optimizing a SELECT using ORDER BY is really hard as there are other restrictions you need to be aware about (like if you're using an index on multiple columns, you can't have a range condition on the first column and sort on the second, at least in MySql).That's why, if performance is an issue, there are ways to workaround the need to sort -- for example you can keep extra data, like page=1 if position is between 0 and 50, page=2 if position is between 50 and 100, and so on, such that LIMITing the query to the first 50 items is WHERE page=1 (basically storage-efficient precached queries - if the conditions are stable, you can do it).
And in cases where you can't fetch the data efficiently in a single query, you're probably doing it wrong (like you chose the wrong data representation - for example the relational model is really awful for describing anything related to graphs).
Of course, I'm not talking about cases when you're fetching unrelated data or cases where performance doesn't matter or cases when you've got BLOBs in your parent :)