wer kann query optimieren

Hallo, diese query taucht bei mir häufig im slow log auf:

mysql> EXPLAIN
-> SELECT f.`id`, f.`firma`, f.`ranking`
-> FROM `bc_firma_kws` k
-> INNER JOIN `bc_firma` f ON (f.`id`=k.`firma`)
->
-> WHERE k.`kw` IN ("test") ORDER BY f.`ranking` DESC
-> ;
+----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
| 1 | SIMPLE | f | ALL | PRIMARY,id_ranking | NULL |
NULL | NULL | 116411 | Using filesort |
| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 36
| schweizmobi.f.id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
2 rows in set (0.00 sec)


`bc_firma_kws` hat PK auf `firma` und `kw`
`bc_firma` hat PK auf `id` mit auto_increment, INDEX `id_ranking` auf
`id` und `ranking`


kann mir jemand sagen, wie man es schaffen kann, das "Using filesort"
weg zu bekommen?

MFG Heiko

Re: wer kann query optimieren

"Heiko (GreenRover) Henning" wrote:
> Hallo, diese query taucht bei mir häufig im slow log auf:
>
> mysql> EXPLAIN
> -> SELECT f.`id`, f.`firma`, f.`ranking`
> -> FROM `bc_firma_kws` k
> -> INNER JOIN `bc_firma` f ON (f.`id`=k.`firma`)
> ->
> -> WHERE k.`kw` IN ("test") ORDER BY f.`ranking` DESC
> -> ;
> +----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
>| id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
>| 1 | SIMPLE | f | ALL | PRIMARY,id_ranking | NULL |
> NULL | NULL | 116411 | Using filesort |
>| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 36
> | schweizmobi.f.id,const | 1 | Using where; Using index |
> +----+-------------+-------+--------+--------------------+-- -------+---------+------------------------+--------+-------- ------------------+
> 2 rows in set (0.00 sec)
>
>
> `bc_firma_kws` hat PK auf `firma` und `kw`

Für diese Query wäre es wesentlich besser, wenn bc_firma_kws einen
UNIQUE INDEX auf (kw, firma) hätte. Als PK nimmst du besser eine
AUTO_INCREMENET Integer Spalte.

> kann mir jemand sagen, wie man es schaffen kann, das "Using filesort"
> weg zu bekommen?

Gar nicht. Da die Tabelle bc_firma über id referenziert wird, kann in
jedem Fall erst das fertige Resultat sortiert werden. Aber ein 'Using
filesort' ist nicht automatisch schlecht.


XL

Re: wer kann query optimieren

Erstmal danke für die Antwort an Axel

Axel Schwenke schrieb:
> Für diese Query wäre es wesentlich besser, wenn bc_firma_kws einen
> UNIQUE INDEX auf (kw, firma) hätte. Als PK nimmst du besser eine
> AUTO_INCREMENET Integer Spalte.

Kannst du mir verraten, wo der Vorteil eines UNIQUE INDEX gegen über dem
PK auf bei dieser Konstellation liegt?

Re: wer kann query optimieren

"Heiko (GreenRover) Henning" wrote:
> Erstmal danke für die Antwort an Axel
>
> Axel Schwenke schrieb:
>> Für diese Query wäre es wesentlich besser, wenn bc_firma_kws einen
>> UNIQUE INDEX auf (kw, firma) hätte. Als PK nimmst du besser eine
>> AUTO_INCREMENET Integer Spalte.
>
> Kannst du mir verraten, wo der Vorteil eines UNIQUE INDEX gegen über dem
> PK auf bei dieser Konstellation liegt?

Der PK für einen Datensatz sollte sich niemals ändern. Das ist selten
gewährleistet, wenn die PK-Spalte(n) reale Eigenschaften des repräsen-
tierten Objekts enthalten.

Der PK muß für Verknüpfungen referenziert werden. Es ist deswegen
sinnvoll, einen möglichst kurzen (und insbesondere einteiligen) PK zu
haben. Sonst dupliziert man unnötig Daten.

Speziell bei InnoDB wird der PK auch als Referenz für alle anderen
Indexe benutzt. Ein langer PK führt hier sogar innerhalb einer Tabelle
zur Duplikation von Daten.


XL