On Thu, May 1, 2008 at 9:54 AM, Larry Garfield <larry@xxxxxxxxxxxxxxxx> wrote: > 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. The fact is, we seldom use join. Because we think it very slow to make it working on two tables with millions of records. Though we did not try it. :( > 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.) > How much is the *a lot*? Thanks. :) > > 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@xxxxxxxxxxxxxxxx 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 > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shelley
