MySQL Forums
Forum List  »  Newbie

Re: Order by using filesort on where
Posted by: Rick James
Date: March 19, 2009 07:11PM

Think of a compound index this way... Consider a directory of people, with thousands, or maybe millions, of names and other info. Let's think of two ways to index it.

Plan A: This is two single-column indexes.
The data is sorted just by last name. So, all the Gonzales lumped together in no order. On the side you have a list of first names. In it, all the Mariels are lumped together in no particular order. How easy would it be to find your entry?

Plan B: This is a compound index, lastname, then firstname. Now how easy is it to find the entry for (Gonzales, Mariel)?

Let's ponder it further. Let's say you need to find all the Mariels. Plan B is useless for this. Yet the second index in Plan A is perfect.

If you needed to search both ways, then two indexes would be warranted:
INDEX(last, first)

By the way, both plans work fine for "find all Gonzales".

Options: ReplyQuote

Written By
Re: Order by using filesort on where
March 19, 2009 07:11PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.