On 9/17/08, Kristian Nielsen <[EMAIL PROTECTED]> wrote: > > "Sheeri K. Cabral" <[EMAIL PROTECTED]> writes: > > > 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 > . > > > Really? I would think that either > > - The result set is small, and can be sorted very quickly in-memory, so > index > or not will not matter much. > > - The result set and/or the total live data is big. In this case, using > the > index for ORDER BY will kill you with disk seeks (even worse if the > index > is not a covering/clustering index). Eg. sorting a whole table bigger > than > buffer pool should be faster with filesort than using index. > > But maybe that thinking is flawed? Or is MySQL filesort really that bad? > Just > curious if you have any further thoughts or real-life experiences with > this...
Kristian, Those are definitely two places where ordering on a set of fields that aren't indexed are OK. However, very often, developers just write queries and don't consider the indexes already in place, or whether or not indexes should be added. In general, most queries I find that sort on non-indexed fields are the kinds where indexes *should* be present, but aren't. So you're right, that's just not the majority of cases I have found.
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
