List:General Discussion« Previous MessageNext Message »
From:Jon Frisby Date:June 8 2002 2:29am
Subject:RE: Not using indexes???
View as plain text  
> * 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

MySQL is making the wrong decision.  As stated below, it's doing a table
scan when it only needs to look at some 2,800 rows out of 970,000 rows.  In
addition, the "real" query I've been trying to make work (a join) gets
nonsense output from EXPLAIN -- some 50k rows from the left table are
involved in the join, yet MySQL estimates that 1 row will be!  (The query
produces correct results, it just takes a very very long time to do so .)


> * 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).

I have never managed to get MySQL to obey the "using index" option.  It
produces no effect here.


> * 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.

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...

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 |
+-------+-------+-----------------+-----------------+---------+------+------
+------------+


> * 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.

However it seems to be doing a table scan across the actual *data*.  It does
not seem to be using the index to eliminate rows.


> 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)

The return result is about 0.3% -- that is a far cry from 30%.  Using ranges
may work (I'll just use 0 and maxint), but this is definitely not desirable
behavior from the MySQL optimizer if I have to resort to such a trick.

Using *a* specific value is somewhat inapplicable here because my ultimate
goal is to use this table in a *join*.  It's not a good thing when the left
table *must* do a table scan across 50k rows, and MySQL acts brain dead when
scanning the right table and looks at all 970k rows even though it should be
getting 1..10 rows out of click for each row from the left table...

-JF

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