Hi. As far as I can see is that you use a condition in your WHERE clause which MySQL will not (yet?) use indexes for. See
http://www.mysql.com/doc/M/y/MySQL_indexes.html (seems to be mainly about MyISAM tables) to see how indexes are used. You use "IS NOT NULL". This page states nowhere that this will be able to make use of an index (only "IS NULL" does). E.g. the non-equivalence operator is the same. MySQL will use indexes for "foo>0", but not "foo<>0", which ask for the same result (presumed foo is an unsigned column). On Fri 2002-06-07 at 19:29:44 -0700, [EMAIL PROTECTED] wrote: [...] > Using sequence_log_id > 0 produces the same result. Using any particular > value in place of 0 produces the same effect. Using sequence_log_id > foo > AND sequence_log_id < bar does seem to use the index... As Erv did not know why this could help: It uses a different operator than "IS NOT NULL", namely greather-than. One, that MySQL supports to make use of indexes. > > explain SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE > sequence_log_id > 100000 and sequence_log_id < 4000000000; > +-------+-------+-----------------+-----------------+---------+------+------ > +------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra | > +-------+-------+-----------------+-----------------+---------+------+------ > +------------+ > | click | range | sequence_log_id | sequence_log_id | 5 | NULL | 10 > | where used | > +-------+-------+-----------------+-----------------+---------+------+------ > +------------+ But type=range does mean that MySQL is using an index. Just in case that this is not clear: "using index" in the "Extra" does not indicate whether an index can be used (a non-NULL value in "key" does), but that the index alone is sufficent and the data file does not has to be touched. Bye, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- 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
