Sheeri K. Cabral wrote:
On 9/16/08, *Brian Aker* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote:Hi! On Sep 16, 2008, at 10:15 PM, Jay Pipes wrote: sort (ORDER BY) or compare (WHERE) values in a column based on a What if you do not have an index but you still want to call ORDERY BY? To me the collation is a property of the column... and possibly we need one on the index as well (in the case of INDEX(A,B).It makes baby Jesus cry when developers order by a field not in an index. Well, not really, but it makes me fume and post things to www.dbawtf.com <http://www.dbawtf.com>.
Sheeri, I think you may be laboring under the assumption that a sort is slower than retrieving records out of physical order. If the database implements clustered indexes and the retrieval is ordered by primary key, then physical order is the collated order and all is well. Otherwise there is a choice of retrieving records in index order (jumping around in the physical file) or reading the records in physical order then sorting them.
Hint: A good disk advertises an average access time of 6 milliseconds and probably lies. A quicksort of a hundred records probably takes a couple of microseconds.
Decent sorts are very, very fast. Even very slow sorts are much faster than disks. Indexes are for optimized retrieval. If the primary key is the same as the given order and the engine uses clustered primary keys *or* the entire retrieval set is known to be in memory, it makes sense to use the index for ordering. Otherwise it is always faster to fetch the records in optimal order then sort them.
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
