Using filesort / temporary - JOIN, GROUP BY, ORDER BY

[ryda]

Hallo zusammen,

die letzten 18 Stunden am Schreibtisch waren echt hart. Eigentlich wollte ich meine Applikation deployen - bis ich einen Blick auf die MySQL Statistiken geworfen habe:

Handler_read_rnd und
Handler_read_rnd_next ziemlich hoch.

So habe ich mich dann auf die Suche gemacht und stehe vor einem Problem, welches ich absolut nicht gelöst bekomme.

Worum geht es:
Ich habe drei Tabellen, die ich über einen JOIN miteinander verbinde.
Ich habe eine WHERE Klausel, ich mache einen GROUP BY, einen ORDER BY.
Und wenn ich mir das SQL erklären (EXPLAIN) lasse, sehe ich "Using temporary, Using filesort".

Und das liegt wohl daran (so weit bin ich schon), dass sich u.a. mein GROUP BY und ORDER BY unterscheiden (hab ich im Handbuch von MySQL nachgelesen, dass dann kein Index mehr greift). ORDER BY RAND() führt auch dazu, dass temp und filesort verwendet werden... Ich möchte aber nicht darauf verzichten, die Ergebnisse sortieren (auch beliebig mit RAND()) zu können, und denke, vielleicht habe ich in meinem DB-Design einen Denkfehler. Mit verschiedensten Indexen habe ich auch schon gespielt...

Letztendlich habe ich in einer Tabelle Reiseberichte namens

REISEN (FELDER: ID, DATUM, BERICHT, STATUS), in einer zweiten eine Liste von Städten namens

STAEDTE (FELDER: ID, NAME) und mit einer dritten Tabelle stelle ich die Verknüpfung her (ein Reisebericht kann sich auf 1...n Städte beziehen):

REISEN_STAEDTE (FELDER: REISEN_ID, STAEDTE_ID).

Wenn ich nun einen

EXPLAIN SELECT
Reisen.ID AS ID,
Reisen.Datum AS Datum,
Reisen.Bericht AS Bericht,
GROUP_CONCAT( Staedte.Name ORDER BY Staedte.Name SEPARATOR ", " ) AS Name
FROM Reisen
JOIN (Reisen_Staedte, Staedte)
ON (Reisen_Staedte.Reisen_ID = Reisen.ID AND Staedte.ID = Reisen_Staedte.Staedte_ID)
WHERE Reisen.Status = 0
GROUP BY Reisen.ID
ORDER BY Reisen.Datum
LIMIT 5

mache, steht es da wie zum Beweis: Filesort/Temporary... Und damit ist zumindest erklärt, woher die beiden hohen Werte (vgl. oben) rühren...

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	Reisen 	ref 	PRIMARY,status 	status 	1 	const 	1 	Using where; Using temporary; Using filesort
1 	SIMPLE 	Reisen_Staedte 	ref 	reise_id 	reise_id 	8 	reiseberichte.Reisen.id 	1 	Using index
1 	SIMPLE 	Staedte 	eq_ref 	PRIMARY 	PRIMARY 	8 	reiseberichte.Reisen_Staedte.staedte_id 	1

Nur: Ich weiß absolut nicht mehr weiter, was ich noch tun könnte, um das zu umgehen???

Vielleicht kann mir hier jemand helfen!

Das wäre super, vielen Dank!

Sebastian

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 03. Mai 2009 um 05:28
-- Server Version: 5.1.30
-- PHP-Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Datenbank: `reiseberichte`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `reisen`
--

CREATE TABLE IF NOT EXISTS `reisen` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `datum` datetime NOT NULL,
  `bericht` text NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=4 ;

--
-- Daten für Tabelle `reisen`
--

INSERT INTO `reisen` (`id`, `datum`, `bericht`, `status`) VALUES
(1, '2009-05-03 05:08:27', 'Super gut', 0),
(2, '2008-05-03 05:08:38', 'Auch nicht schlecht', 1),
(3, '2009-05-03 05:08:56', 'Nicht so doll', 0);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `reisen_staedte`
--

CREATE TABLE IF NOT EXISTS `reisen_staedte` (
  `reisen_id` bigint(20) NOT NULL,
  `staedte_id` bigint(20) NOT NULL,
  KEY `reise_id` (`reisen_id`,`staedte_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `reisen_staedte`
--

INSERT INTO `reisen_staedte` (`reisen_id`, `staedte_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(3, 1),
(3, 5);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `staedte`
--

CREATE TABLE IF NOT EXISTS `staedte` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Daten für Tabelle `staedte`
--

INSERT INTO `staedte` (`id`, `name`) VALUES
(1, 'Hamburg'),
(2, 'Leipzig'),
(3, 'Berlin'),
(4, 'Dresden'),
(5, 'Hannover');

[TBT]

erweitere mal den Index 'status' der Tabelle 'Reisen' um ID und Datum

[ryda]

Hallo TBT,

sehe gerade, dass das doch nicht funktioniert.
Auch mit FORCE INDEX (status) geht es nicht...

Vielen Dank!

Seb.

[TBT]

ALTER TABLE `Reisen` DROP INDEX `status` ,
ADD INDEX `status` ( `status` , `datum` ) ;

Entsorgt erstmal das "using filesort"

[ryda]

Ah, merci merci merci,

that did the trick.

Habe jetzt doch ne ganze Menge gelernt, was das Thema Indexe angeht. Vielen Dank!