Jon,
Have you tried phrasing your WHERE clause as
WHERE sequence_log_id >= 0 ?
If there is a determinate lower bound other than zero, then substitute it
for 0 as the second comparand.
Let us know how it comes out.
(And however it comes out, don't ask me why. The real experts can sort
that out for both of us when they get to their desks, in about 4 hours' time.)
--Erv
At 06:07 PM 6/7/2002 -0700, Jon Frisby wrote:
>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
