welcher Key wird wann benutzt ?

[Stephan Bulheller]

Hallo Manfred,

On Tue, 2002-07-09 at 15:30, Manfred Wallpaper wrote:
> warum benutzt das Query: "select browser from log group by browser;"
> in der tabelle:
> explain log;
> +-------------+------------------+------+-----+---------+-------+
> | Field       | Type             | Null | Key | Default | Extra |
> +-------------+------------------+------+-----+---------+-------+
> | browser     | char(255)        |      | MUL |         |       |
> +-------------+------------------+------+-----+---------+-------+
> 
> keinen key ?

Du suchst hier ueber alle Datensaetze, da Du den Select nicht mit einem
WHERE einschraenkst. 

> explain select count(browser) as A, browser from log where browser!='' group by browser order by A DESC;
> +-------+------+---------------+------+---------+------+---------+------------+
> | table | type | possible_keys | key  | key_len | ref  | rows    | Extra      |
> +-------+------+---------------+------+---------+------+---------+------------+
> | log   | ALL  | NULL          | NULL |    NULL | NULL | 1428518 | where used |
> +-------+------+---------------+------+---------+------+---------+------------+
> 1 row in set (0.00 sec)
> 
> oder anders auch
> 
> explain select browser from log group by browser;
> +-------+------+---------------+------+---------+------+---------+-------+
> | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
> +-------+------+---------------+------+---------+------+---------+-------+
> | log   | ALL  | NULL          | NULL |    NULL | NULL | 1428518 |       |
> +-------+------+---------------+------+---------+------+---------+-------+
> 1 row in set (0.00 sec)
> 
> 
> Versteh ich nicht, es gibt doch einen key auf 'browser' ?

Auch steht folgendes in der Doku:
http://www.mysql.com/doc/M/y/MySQL_indexes.html
Note that in some cases MySQL will not use an index, even if one would
be available. Some of the cases where this happens are:

* If the use of the index would require MySQL to access more than 30% of
the rows in the table. (In this case a table scan is probably much
faster, as this will require us to do much fewer seeks.) Note that if
such a query uses LIMIT to only retrieve part of the rows, MySQL will
use an index anyway, as it can much more quickly find the few rows to
return in the result.
* If the index range may contain NULL values and you are using ORDER BY
... DESC

Das bedeutet auch, dass weniger als 30% ueber den Schluessel
identifiziert werden muessen, damit der Schluessel trifft, weil
ansonsten der Table-Scan effizienter ist.

Gruss,
Stephan


---
Infos zur Mailingliste, zur Teilnahme und zum An- und Abmelden unter
-->>  http://www.4t2.com/mysql