List:General Discussion« Previous MessageNext Message »
From:bvyas3 Date:June 8 2002 4:50am
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
* 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" <jfrisby@stripped>
To: <mysql@stripped>
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 <mysql-thread111373@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-bvyas3=comcast.net@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

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