List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:June 8 2002 1:11pm
Subject:Re: Not using indexes???
View as plain text  
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, jfrisby@stripped 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.

-- 
benjamin-mysql@stripped
Thread
Maximum JOINED tablesArthur Fuller5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • RE: Maximum JOINED tablesAndrew Hazen5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
RE: Maximum JOINED tablesWalt Weaver5 Jun
  • Re: Maximum JOINED tablesSabine Richter5 Jun
    • Re: Maximum JOINED tablesDan Nelson5 Jun
  • Re: Maximum JOINED tablesRoger Baklund5 Jun
    • Re: Maximum JOINED tablesHarald Fuchs6 Jun
      • RE: Maximum JOINED tablesRoger Baklund6 Jun
        • Re: RE: Maximum JOINED tablesVictoria Reznichenko6 Jun
          • Re: Maximum JOINED tablesRoger Baklund6 Jun
            • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
              • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Re: Maximum JOINED tablesVictoria Reznichenko7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund7 Jun
                • Re: Maximum JOINED tablesBenjamin Pflugmann7 Jun
                  • Re: Maximum JOINED tablesRoger Baklund8 Jun
                    • Not using indexes???Jon Frisby8 Jun
                      • Re: Not using indexes???Erv Young8 Jun
                      • Re: Not using indexes???bvyas38 Jun
                        • RE: Not using indexes???Jon Frisby8 Jun
                          • Re: Not using indexes???Benjamin Pflugmann8 Jun
                            • Re: Not using indexes???Erv Young10 Jun
                            • RE: Not using indexes???Jon Frisby10 Jun
                              • php+mysql+multiple lines qrycristian ditoiu10 Jun
                                • Re: php+mysql+multiple lines qryBrent Baisley10 Jun
                                  • Re: php+mysql+multiple lines qrycristian ditoiu10 Jun