wk writes:
> Could anybody explain why index stops to work > in the query > SELECT SUM(...) WHERE ... > with encreasing the size of range, but continue to work > in the analog query > SELECT COUNT(*) WHERE ... ? Well, I can imagine that the optimizer chooses not to use the index for SUM() a lot sooner than from COUNT() since it will have to actually look at the values. Ideally, the time for a "COUNT() WHERE value BETWEEN this AND that" takes logarithmic time (log of the total number of rows in the table, that is), no matter how many rows are matched. SUM(), on the other hand, takes time proportional to the number of matched rows, and the overhead of using an index is something like a factor 2 or more of the time it takes to just use the data for the same number of rows. Thus if your WHERE matches more than a third of the rows, using the index is a bad idea. This is why it's pointless to index boolean columns, unless you have a very uneven distribution and run ANALYZE TABLE while you have such a distribution. Question to the list: Is the last part of what I said correct? //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
