* Mysql, after reading the query, decides wether using an index would be better than just a table row scan. Hence, it's MySql's decision * You can force MySql to use indexes using the 'using index' option with the select query.(MySql 'might' still reject the force, not sure of the circumstances). * The first 2 queries pretty much won't use index because it is not a specific value that you are asking MySql to search for but just a NOT NULL, very different for '=' or even ranges. * The final query is a Group By and hence it will need to use the index to group by the Values that it is going to show you. * The third query is a bit confusing. I don't think it should be using indexes, just as the second one but it is due to some reason, can't answer that one but instead I would also suggest that you try this: explain Select sum(actual_revenue) From Click where sequence_log_id = "specifysomevalue"; That should use indexes since you are searching for something specific. You can also try ranges and it should still use indexes ( a rule of thumb is that if the return result is more than 30% of the total number of records, it's faster to do just a full table scan so it still might not use it if you specify a very big range)
Regards, Bhavin Vyas. ----- Original Message ----- From: "Jon Frisby" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 07, 2002 6:07 PM Subject: Not using indexes??? > Please excuse me if this is something blindingly obvious, but having now > encountered this in several circumstances, but I have been unable to find a > resolution in the docs (perhaps I just missed it?). > > In actuality we encountered the problem when doing some nasty joins, but the > problem seems really to be more fundamental as we've reproduced the problem > without a join... > > We're using MySQL 4.0.1 on Linux (from the RPM). We have an InnoDB table, > 'click' with 6 or so indexes on it. Notably: > -`id` is the PRIMARY KEY. > -`sequence_log_id` has a non-unique index. > > The problem is when we do queries that involve columns *not* available > directly from the index, MySQL absolutely refuses to use an index for > looking up rows. See the output from EXPLAIN for each of these queries: > > SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL; > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra | > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > | click | index | sequence_log_id | sequence_log_id | 5 | NULL | > 939470 | where used; Using index | > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > > > SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT > NULL; > +-------+------+-----------------+------+---------+------+--------+--------- > ---+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +-------+------+-----------------+------+---------+------+--------+--------- > ---+ > | click | ALL | sequence_log_id | NULL | NULL | NULL | 939470 | where > used | > +-------+------+-----------------+------+---------+------+--------+--------- > ---+ > (`actual_revenue` isn't part of the index. > > > SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL; > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra | > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > | click | index | sequence_log_id | sequence_log_id | 5 | NULL | > 939470 | where used; Using index | > +-------+-------+-----------------+-----------------+---------+------+------ > --+-------------------------+ > (Since it's an InnoDB table, a secondary index uses the PK as a reference > for the index.) > > > SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT > NULL GROUP BY sequence_log_id; > +-------+-------+-----------------+-----------------+---------+------+------ > --+------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra | > +-------+-------+-----------------+-----------------+---------+------+------ > --+------------+ > | click | index | sequence_log_id | sequence_log_id | 5 | NULL | > 939469 | where used | > +-------+-------+-----------------+-----------------+---------+------+------ > --+------------+ > (The key isn't actually *used* here, as demonstrated by the query taking a > long time to run... Of the ~970k rows in click, about 2800 have > sequence_log_id IS NOT NULL.) > > > > My question is: Is this a bug? A feature? What can I do to get MySQL to > use the sequence_log_id index? > > -JF > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
