List:General Discussion« Previous MessageNext Message »
From:Matthias Pigulla Date:August 5 2000 3:43pm
Subject:Why does MySQL not use this index?
View as plain text  
Hi folks,

during the last days, I have run into this problem several times, always in
different contexts with different queries and databases, but the result is
always the same.

When I EXPLAIN my queries, I get output similar to this one:

+------------+-------+---------------+---------+---------+------------------
-+------+------------+
| table      | type  | possible_keys | key     | key_len | ref
| rows | Extra      |
+------------+-------+---------------+---------+---------+------------------
-+------+------------+
| menudoc    | const | PRIMARY,menu  | PRIMARY |       2 | ???
|    1 |            |
| submenudoc | const | PRIMARY,menu  | PRIMARY |       2 | ???
|    1 |            |
| menu       | ref   | menu          | menu    |       5 | y,menudoc.kat,???
|    8 | where used |
| submenu    | ALL   | menu          | NULL    |    NULL | NULL
|   42 | where used |
+------------+-------+---------------+---------+---------+------------------
-+------+------------+

I don't want to annoy you with the query that caused this; anyway, I wonder
why the type attribute is set to "ALL" in the last row although the "menu"
key could be used?

With other words - I did not miss to define an appropriate index, right?

Has that something to do with the statement "Each table index is queried and
the best index that spans fewer than 30% of the rows is used. If no such
index can be found, a quick table scan is used." I found in some newsgroup?

Should mention that "menu" and "submenu" are "LEFT JOINed ON"?. If
necessary, I can post the query, although I've had several different queries
that caused problems of this type.

Any help on this is appreciated - thanks a lot!

Matthias
--

 w e b f a c t o r y   G m b H
   Matthias Pigulla <mp@stripped> - Geschaeftsfuehrer
   Lessingstr. 60 - D-53113 Bonn - Germany - www.webfactory.de
   Fon +49(0)228-9114455 - Fax +49(0)228-9114499 - ICQ 49185492

Thread
Why does MySQL not use this index?Matthias Pigulla5 Aug
  • Re: Why does MySQL not use this index?sinisa6 Aug