I agree with all your points. Indexes are very useful, even necessary in some situations. On the other hand, they also use up diskspace, and can slow INSERTs and UPDATEs. If you have a very small database, you may simply not need them.
Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- > -----Original Message----- > From: Chris Nolan [mailto:[EMAIL PROTECTED] > Sent: Monday, December 15, 2003 7:09 PM > To: Joshua Thomas > Cc: 'Dan Anderson'; Chris Elsworth; [EMAIL PROTECTED] > Subject: Re: Questions about indexing > > > Even if your database fits entirely in memory, not having indexes in > place would not be a good idea. > > In an interview Monty did regarding in-memory databases, he very > specifically made the point that where > your database is sitting will never remove the need for > various types of > index. > > From some extremely boring computer science/software engineering > subject I did a few years back: > > 1. If there's no index to play with, you'll need to do a full > table scan > to find the rows matching the > conditions predicated in your WHERE clause. > 2. If there is an index or two to play with, you'll at the very least > cut down the number of rows that > will be read from disk for further inspection should the optimiser > choose to use them (look up FORCE INDEX(...) > in MySQL) > 3. For B-Tree and B+Tree indexes, you're looking at an average of 1-2 > probes (with a probe being roughly > the same expense I/O-wise as reading a row) for finding an > item matching > an "=" condition and the same for > finding the beginning/end of a range for range-style queries (eg: ... > WHERE a > 20 AND a < 40). > > Hope this helps! > > Regards, > > Chris > > Joshua Thomas wrote: > > >Find out the real way: Use EXPLAIN and BENCHMARK() commands > to get the speed > >of your operations with and without an index. That's the > only way to know > >for certain. > > > >Cheers, > > > >Joshua Thomas > >Network Operations Engineer > >PowerOne Media, Inc. > >tel: 518-687-6143 > >[EMAIL PROTECTED] > > > >--- > >In theory there is no difference between theory and > practice. In practice > >there is. > >- Yogi Berra > >--- > > > > > > > > > > > >>-----Original Message----- > >>From: Dan Anderson [mailto:[EMAIL PROTECTED] > >>Sent: Monday, December 15, 2003 1:18 PM > >>To: Chris Elsworth > >>Cc: [EMAIL PROTECTED] > >>Subject: Re: Questions about indexing > >> > >> > >> > >> > >>>With such a small database it really boils down to just being tidy; > >>>you don't want indexes you're not going to use. > >>> > >>> > >>Well the database is going to be like 200MB and executing several > >>hundred queries a minute. Thus my concern about speed. Is a > >>P4 w/ 1GB > >>RAM going to choke and die, or will indexing help out? > >> > >>Thanks in advance, > >> > >>Dan > >> > >> > >>-- > >>MySQL General Mailing List > >>For list archives: http://lists.mysql.com/mysql > >>To unsubscribe: > >>http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> > >> > > > > > > >
