Re: Best practices for using MySQL index

On Wednesday 30 April 2008, Chris wrote: > >> Index on most integer fields only. Text fields can be indexed, but is > >> not important when you design your DB well. > >> > >> Don't index just all integer fields. Keep track of the cardinality of a > >> column. If you expect a field to have 100.000 records, but with only 500 > >> distinct values it has no use to put an index on that column. A full > >> record search is quicker. > > > > Hmmm... That's new. :) > > To explain that further the idea is that if you have something like a > 'status' field which can only hold 5 values, there's no point indexing > it if there's a reasonably even spread. > > If you could only ever have a handful of fields with a status code of > '1', then it's worth indexing if you have to find those particular > records quickly. I don't think mysql supports partial indexes, but some > databases do so you only index the fields that match a certain criteria. > > I'd suggest a more thorough approach to working out what to index rather > than just trying to guess what's going on. Another piece of low-hanging-fruit is to index a field that you will be joining on frequently. Point above about spread still applies, but if you can join index to index, the join goes a lot faster. (A primary key in MySQL is always indexed.) Having too many indexes rarely if ever costs on read (as far as I am aware), but it does cost on write to update the index. How much that matters is use-case specific. -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson