Subselect optimieren...

Subselect optimieren...

am 04.02.2007 19:17:16 von plutoplanet

Hello World!

Ich habe zwei Tabellen: Eine für die Kunden und eine aller ihrer
Bestellungen. Letztendlich will nur die letzten Bestellungen meiner
Kunden sehen. Die Query hab ich unten angeführt. Bei höheren
Datenmengen ist diese aber sehr, sehr langsam. Kann mir da jemand
helfen?

Kundentabelle:
CREATE TABLE Customer (
Id INTEGER NOT NULL AUTO_INCREMENT,
Name VARCHAR(10),
INDEX (Id),
PRIMARY KEY (Id)
) ENGINE=3DInnoDB;

INSERT INTO Customer ( Name ) VALUES ( 'Peter' );
INSERT INTO Customer ( Name ) VALUES ( 'Paul' );
INSERT INTO Customer ( Name ) VALUES ( 'Mary' );
INSERT INTO Customer ( Name ) VALUES ( 'John' );
INSERT INTO Customer ( Name ) VALUES ( 'Travolta' );

SELECT * FROM Customer;
+----+----------+
| Id | Name |
+----+----------+
| 1 | Peter |
| 2 | Paul |
| 3 | Mary |
| 4 | John |
| 5 | Travolta |
+----+----------+

Tabelle mit Bestellungen:
CREATE TABLE Orders (
Id INTEGER NOT NULL AUTO_INCREMENT,
Date DATETIME,
CustomerId INTEGER NOT NULL,
INDEX (Id),
INDEX (Date),
INDEX (CustomerId),
FOREIGN KEY (CustomerId) REFERENCES Customer (Id),
PRIMARY KEY (Id)
) ENGINE=3DInnoDB;

Speichern wir mal ein paar Zufallsdaten in die Bestelltabelle:
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );
INSERT INTO Orders ( DATE, CustomerId ) VALUES ( DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) ) );


mysql> SELECT * FROM Orders ORDER BY CustomerId, Date;
+----+---------------------+------------+
| Id | Date | CustomerId |
+----+---------------------+------------+
| 3 | 2006-12-09 20:57:05 | 1 |
| 2 | 2007-01-11 16:57:05 | 1 |
| 8 | 2007-01-18 08:57:05 | 1 |
| 10 | 2007-02-02 15:57:05 | 1 |
| 5 | 2007-01-31 16:57:05 | 2 |
| 9 | 2006-12-01 09:57:05 | 4 |
| 6 | 2006-12-22 20:57:05 | 4 |
| 4 | 2007-01-04 05:57:05 | 4 |
| 1 | 2007-01-19 00:57:05 | 4 |
| 7 | 2006-12-18 17:57:05 | 5 |
| 11 | 2006-12-31 10:57:05 | 5 |
+----+---------------------+------------+
11 rows in set (0.00 sec)

Wenn ich nun die jeweils letzten Bestellungen der Kunden Abfragen
will, dann tu ich das so:

SELECT
Customer.Id, Customer.Name,
Orders.Id, Orders.Date
FROM Orders
JOIN
(
SELECT CustomerId, MAX( Date ) AS OrdersDate
FROM Orders
GROUP BY CustomerId
) MaxOrder
ON Orders.CustomerId =3D MaxOrder.CustomerId AND Orders.Date =3D
MaxOrder.OrdersDate
JOIN Customer ON Customer.Id =3D Orders.CustomerId
GROUP BY Orders.CustomerId
ORDER BY Customer.Id
;

+----+----------+----+---------------------+
| Id | Name | Id | Date |
+----+----------+----+---------------------+
| 1 | Peter | 10 | 2007-02-02 15:57:05 |
| 2 | Paul | 5 | 2007-01-31 16:57:05 |
| 4 | John | 1 | 2007-01-19 00:57:05 |
| 5 | Travolta | 11 | 2006-12-31 10:57:05 |
+----+----------+----+---------------------+
5 rows in set (0.00 sec)

Das ist toll, denn das Ergebnis ist richtig!
Da wir das ganze aber nicht nur zum Spass machen, und wir uns viele
Bestellungen erwarten, werde ich diese mal vervielfachen:

INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;
INSERT INTO Orders ( DATE, CustomerId ) SELECT DATE_SUB( NOW(),
INTERVAL FLOOR( RAND() * 2000 ) HOUR ), FLOOR( 1 + ( RAND() * 5 ) )
FROM Orders;


SELECT COUNT(*) FROM Orders;
+----------+
| COUNT(*) |
+----------+
| 720896 |
+----------+
1 row in set (0.33 sec)


Bei der Abfrage nach den letzten Bestellungen bekomme ich auch ein
richtiges Ergebnis, aber es lässt mich sehr lange warten:

+----+----------+--------+---------------------+
| Id | Name | Id | Date |
+----+----------+--------+---------------------+
| 1 | Peter | 364660 | 2007-02-04 18:58:18 |
| 2 | Paul | 369953 | 2007-02-04 18:58:18 |
| 3 | Mary | 417947 | 2007-02-04 18:58:18 |
| 4 | John | 396817 | 2007-02-04 18:58:18 |
| 5 | Travolta | 366755 | 2007-02-04 18:58:18 |
+----+----------+--------+---------------------+
5 rows in set (6.77 sec)

Kann meine Query in irgendeiner Weise noch optimiert werden?

Danke,
Herwig

Re: Subselect optimieren...

am 04.02.2007 21:01:32 von Andreas Kretschmer

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Subselect optimieren...

am 05.02.2007 08:47:30 von Phil Meier

>
> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
> das Explain Deines Selects inhalieren können.

Warum diese hässlichen Worte? Da sucht einer Hilfe und wer keine geben
will der soll schweigen!

Phil

Re: Subselect optimieren...

am 05.02.2007 09:05:22 von Christian Kirsch

Phil Meier schrieb:
>> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
>> das Explain Deines Selects inhalieren können.
>
> Warum diese hässlichen Worte? Da sucht einer Hilfe und wer keine geben
> will der soll schweigen!

Was wäre denn damit geholfen? So weiß er wenigstens, was seiner "Frage"
fehlte.

Re: Subselect optimieren...

am 05.02.2007 09:18:53 von Phil

On Feb 5, 9:05 am, Christian Kirsch wrote:
> Phil Meier schrieb:
>
> >> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
> >> das Explain Deines Selects inhalieren können.
>
> > Warum diese hässlichen Worte? Da sucht einer Hilfe und wer keine geben
> > will der soll schweigen!
>
> Was wäre denn damit geholfen? So weiß er wenigstens, was seiner "Frag=
e"
> fehlte.

"monsterhaft", "Scheiß", "reinblasen", "inhalieren". Das sind alles
Worte die zur Klärung von PlutoPlanet's Frage nichts beitragen - und
deshalb überflüssig sind.

Aber PlutoPlanet selber müsste sich wohl melden - ob ihm der Beitrag
von Andreas geholfen hat. Oder ob ihn die Antwort nur aus der Gruppe
vertrieben hat.

Re: Subselect optimieren...

am 05.02.2007 09:45:30 von Thomas Rachel

PlutoPlanet wrote:
^^^^^^^^^^^ Was für Ding?

[snip]

> Kann meine Query in irgendeiner Weise noch optimiert werden?

Standardrückfrage: Was sagt denn EXPLAIN dazu?


Thomas
--
Wir nehmen Hauptkreditkarten an: Der ausdrückliche Amerikaner, entdecken,
MasterCard oder Visum. Wir Dose E-mail es in Ihrem gewählten Format.
CI-Corporation, Washington, Gleichstrom USA.
(Aus einer www-Seite eines Übersetzungsbüros)

Re: Subselect optimieren...

am 05.02.2007 09:49:43 von Thomas Rachel

Andreas Kretschmer wrote:

> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
> das Explain Deines Selects inhalieren können.

s/Anstatt/Zusätzlich zur/
s/hier monsterhaft Scheiß reinzublasen/Angabe, wie das Problem reproduziert
werden kann/


Thomas

Re: Subselect optimieren...

am 05.02.2007 10:32:13 von Christian Kirsch

Am 05.02.2007 09:18 schrieb Phil:
> On Feb 5, 9:05 am, Christian Kirsch wrote:
>> Phil Meier schrieb:
>>
>>>> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
>>>> das Explain Deines Selects inhalieren können.
>>> Warum diese hässlichen Worte? Da sucht einer Hilfe und wer keine geben
>>> will der soll schweigen!
>> Was wäre denn damit geholfen? So weiß er wenigstens, was seiner "Frage"
>> fehlte.
>
> "monsterhaft", "Scheiß", "reinblasen", "inhalieren". Das sind alles
> Worte die zur Klärung von PlutoPlanet's Frage nichts beitragen - und
> deshalb überflüssig sind.
>

Wald? Echo?

Wer sich derart dreist über die Gepflogenheiten in Newsgroups
hinwegsetzt, muss halt damit leben, dass ihn jemand deutlich darauf
hinweist. Und warum hast Du den korrekten Hinweis von Andreas auf das
unterschlagen, was der OP *tun* soll?

Re: Subselect optimieren...

am 05.02.2007 10:39:22 von Florian Laws

On 2007-02-05, Christian Kirsch wrote:
> Am 05.02.2007 09:18 schrieb Phil:
>> On Feb 5, 9:05 am, Christian Kirsch wrote:
>>> Phil Meier schrieb:
>>>
>>>>> Anstatt hier monsterhaft Scheiß reinzublasen hättest Du einfach mal
>>>>> das Explain Deines Selects inhalieren können.
>>>> Warum diese hässlichen Worte? Da sucht einer Hilfe und wer keine geben
>>>> will der soll schweigen!
>>> Was wäre denn damit geholfen? So weiß er wenigstens, was seiner "Frage"
>>> fehlte.
>>
>> "monsterhaft", "Scheiß", "reinblasen", "inhalieren". Das sind alles
>> Worte die zur Klärung von PlutoPlanet's Frage nichts beitragen - und
>> deshalb überflüssig sind.
>>
>
> Wald? Echo?
>
> Wer sich derart dreist über die Gepflogenheiten in Newsgroups
> hinwegsetzt, muss halt damit leben, dass ihn jemand deutlich darauf
> hinweist.

Hier wurde sich nicht in einer Weise "dreist" über "Gepflogenheiten"
in Newsgroups hinweggesetzt, die solche Unverschämtheiten als Antwort
rechtfertigen könnte.

Grüße,

Florian

Re: Subselect optimieren...

am 05.02.2007 11:17:18 von Andreas Kretschmer

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Subselect optimieren...

am 05.02.2007 12:27:07 von Phil

...
> Er bekam Hilfe. Von mir. Nicht von Dir.
Schön dass Du geholfen hast. Das war zum Zeitpunkt meines Schreibens
allerdings nicht ersichtlich!

> Pawlowsche Reflexe sinnfrei
> Betroffener kannst Du Dir sparen.
Da bin ich und andere offensichtlich anderer Meinung.

> Kostenlose freundliche Hilfe gibt es
> bei pampigen Auftreten nur gegen gute Bezahlung.
Das trifft auf Dich wohl zu.

...
Phil

Re: Subselect optimieren...

am 05.02.2007 12:35:33 von Andreas Kretschmer

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Subselect optimieren...

am 05.02.2007 13:00:52 von Phil

> ...
> Du hättest das, was Du zitiert hast, nur lesen brauchen. Wenn Du es
> nicht verstehst, was ein Explain ist, dann verhalt Dich solange still,
> bis Du es im Handbuch gefunden hast.
>
> Und wieder merk ich hier: kapotter Realname als Indikator für sinnfreien
> Inhalt des Postings.

Mit dem Explain und meinem Wissen darüber liegst Du falsch.

Dass Du im Nebensatz einen Tipp gegeben hast - da bin ich Deiner
Meinung. Dein Posting von 11:17 enthält allerdings noch weitere
Information die zum Zeitpunkt meiner ersten Bemerkung (8:47) nicht
vorhanden waren.

Das Leben ist doch so einfach - wenn man unfehlbar ist und nur die
anderen sich nicht brauchbar verhalten.

Phil

Re: Subselect optimieren...

am 09.02.2007 10:22:42 von plutoplanet

Hm, hab mich bisher nicht äussern wollen.
Die ganzen Klugscheissereien über was bei Postgre geht oder nicht geht
haben mir nicht wirklich was gebracht.
Falls jemand doch eine Antwort hätte, wäre ich sehr dankbar.
Herwig. (richtiger Name, für die, die das dringend brauchen).