MySQl-Query läuft "ewig"

[dispy]

Moin,

hab meinen Account mal reaktiviert :)

Ich hab jetzt schon in 2 Foren gefragt, hoffe, dass ihr evt. eine Lösung findet :)

Problem:
Der (folgende) Query läuft "ewig" durch -nach 10 min bekomme ich dann die Meldung "MySQl-Server has gone away". Ich =>ratlos, da der Query wirklich gut aussieht.

SQL-Code:

SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
      INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038 OR loser.ally=37038 )
      ORDER BY conquer.timestamp DESC LIMIT 50

Strukturen der Tabellen:

Zitat:

CREATE TABLE IF NOT EXISTS `de12_tribe` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL default '',
`ally` int(11) NOT NULL,
`villages` int(5) NOT NULL,
`points` int(11) NOT NULL,
`rank` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `de12_conquer` (
`villageid` int(11) NOT NULL,
`timestamp` int(11) NOT NULL,
`new_owner` int(11) NOT NULL,
`old_owner` int(11) NOT NULL,
KEY `villageid` (`villageid`),
KEY `timestamp` (`timestamp`),
KEY `new_owner` (`new_owner`),
KEY `old_owner` (`old_owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Und jetzt wirds richtig interessant: Folgender Query funktioniert nämlich:

SQL-Code:

SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038  )
      ORDER BY conquer.timestamp DESC LIMIT 50

Also nur ein INNER JOIN auf die gleiche Tabelle mehr und der ganze Query braucht ewig..... ??

Ich habe statt INNER JOIN auch schon LEFT und RIGHT JOIN probiert- das funzt auch nicht.

Einträge in de12_conquer:625,687
de12_tribe: 7,616

MySQL-Explain

SQL-Code:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    loser    ALL    PRIMARY    NULL    NULL    NULL    7616    Using temporary; Using filesort
1    SIMPLE    conquer    ref    timestamp,new_owner,old_owner    old_owner    4    dispy.loser.id    8    Using where
1    SIMPLE    conqueror    ALL    PRIMARY    NULL    NULL    NULL    7616    Range checked for each record (index map: 0x1)

Bin echt ratlos und zwei andere Foren waren das auch -ich hoffe ich kann das Proble mit eurer Hilfe endlich lösen :)

Vielen DAnk im Voraus !

MfG
dispy

[alzaimar]

1. Indexiere mal alle Felder, die an einem JOIN beteiligt sind.
2. Kann MySQL temporäre Tabellen? Dann mach das in zwei Schritten

[dispy]

Zitat:

Zitat von alzaimar

1. Indexiere mal alle Felder, die an einem JOIN beteiligt sind.
2. Kann MySQL temporäre Tabellen? Dann mach das in zwei Schritten

Ja, kann es: Aber für jeden Query ne temp anzulegen wäre bisschen extrem aufwändig oder? Das mit vollen Indexieren mach ich mal ^^

MfG
dispy

[sx2008]

Folgende Felder sollten indiziert werden:
de12_conquer.new_owner
de12_conquer.old_owner
de12_conquer.timestamp // sollte entscheidenden Betrag zum Sortieren liefern
de12_tribe.id // index nur nötig, wenn id nicht eh schon Primärschlüssel ist
de12_tribe.ally

[alzaimar]

Zitat:

Zitat von dispy

Zitat:

Zitat von alzaimar

...2. Kann MySQL temporäre Tabellen? Dann mach das in zwei Schritten

Ja, kann es: Aber für jeden Query ne temp anzulegen wäre bisschen extrem aufwändig oder?

Da hast Du natürlich Recht. Daher ist das Punkt 2. Wenn Punkt 1 nicht funktioniert, bleibt Dir aber -außer mehr RAM- nichts Anderes übrig...

[franktron]

Der Fehler ist ganz einfach de12_conquer hat keine Primarindex das mag MySQL garnicht

[alzaimar]

Wieso funktioniert dann die zweite Query?

[franktron]

Zitat:

Zitat von alzaimar

Wieso funktioniert dann die zweite Query?

Gute Frage. Aber in der MySQL Doku steht das man immer ein PI habe sollte

[alzaimar]

[OT] PK / PI sind grundsätzlich zu vergeben, einfach damit jeder Datensatz eindeutig identifiziert werden kann. Ohne PK hast Du keine Möglichkeit, bei zwei identischen Datensätzen nur einen zu löschen, ohne die Tabelle komplett neu zu bauen. [/OT]

[dispy]

Zitat:

Zitat von alzaimar

[OT] PK / PI sind grundsätzlich zu vergeben, einfach damit jeder Datensatz eindeutig identifiziert werden kann. Ohne PK hast Du keine Möglichkeit, bei zwei identischen Datensätzen nur einen zu löschen, ohne die Tabelle komplett neu zu bauen. [/OT]

Das ist leider nicht möglich.... sonst hätte ich das schon gemacht ^^

Ich habe alle Felder die beteiligt sind nun indexiert- trotzdem lädt der Query ewig -.-
Habt ihr noch weitere Ideen ?

MfG
dispy

[franktron]

Mach doch einen Autoinc PI so mach ich das immer

[dispy]

Was ist ein Autoinc PI ? o.O

[DeddyH]

Das ist ein künstlicher Schlüssel, der automatisch inkrementiert wird.

[dispy]

das bringt doch gar nix... der wird doch nicht abgefragt..
MfG
dispy

[franktron]

Das ist egal mach es doch einfach mal und teste es.

[dispy]

o.O

SQL-Code:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Da is weder schon nen Primary drin noch irgendein andereres Feld mit auto_increment ??!!

MfG
dispy

[marabu]

Hallo,

AUTOINC verwendet einen internen Schlüsselgenerator, du hast aber scheinbar deinen eigenen für das Feld ID in der Tabelle DE12_TRIBE. Ob du das umstellen willst bleibt dir überlassen, an der Laufzeit deiner Query ändert sich deshalb nichts.

Ein Primärschlüsssel für die Tabelle DE12_CONQUER wäre aber wichtig, damit die Joins flutschen. Dazu musst du die Spalte TIMESTAMP als primary key deklarieren - in der Hoffnung, dass die Spaltenwerte unique sind. Ansonsten rettet dich nur noch ein zusammengesetzter Schlüssel oder eben das Einführen einer neuen Spalte ID AUTOINC.

Freundliche Grüße

[dispy]

Moin,
wie generiere ich einen zusammengesetzten SChlüssel ? Timestamp KANN unique sein, MUSS es aber nicht -daher zusammengesetzt

MfG
dispy

[marabu]

Einfach alle Felder auflisten:

SQL-Code:

... PRIMARY KEY (villageid, timestamp) ...

[dispy]

SQL-Code:

 ALTER TABLE `de12_conquer` ADD PRIMARY KEY ( villageid, timestamp )

=>

Zitat:

#1062 - Duplicate entry '3401-1178040940' for key 1

Sorry aber ich schaffs einfach nich :wall:

MfG
dispy

[marabu]

Hi,

wenn du reservierte Wörter als Feldnamen benutzt, dann musst du die Bezeichner korrekt quoten:

SQL-Code:

ALTER TABLE de12_conquer
ADD PRIMARY KEY (villageid, `timestamp`)

Freundliche Grüße

[dispy]

uh ganz verpennt..... thx :)

EDIT: der neue Primary-Key bringt auch keine Lösung: der Query läuft trotzdem ewig

MfG
dispy

[omata]

SQL-Code:

SELECT c.timestamp AS time,
       c.new_owner AS new_owner_id
FROM de12_conquer c
INNER JOIN de12_tribe x
  ON    c.new_owner = x.id
     OR c.old_owner = x.id
WHERE (c.timestamp > 0 && c.timestamp < 1213647029734) 
  AND (x.ally = 37038)
ORDER BY c.timestamp DESC LIMIT 50

[dispy]

Zitat:

Zitat von omata

SQL-Code:

SELECT c.timestamp AS time,
       c.new_owner AS new_owner_id
FROM de12_conquer c
INNER JOIN de12_tribe x
  ON    c.new_owner = x.id
     OR c.old_owner = x.id
WHERE (c.timestamp > 0 && c.timestamp < 1213647029734) 
  AND (x.ally = 37038)
ORDER BY c.timestamp DESC LIMIT 50

Der läuft auch ewig durch.
Aber trotzdem danke

MfG
dispy

[marabu]

Hallo dispy,

lass mal den Range-Check für die Spalte "timestamp" in der WHERE-Klausel weg - da stehen blödsinnige Werte drin, wenn ich das richtig sehe.

Freundliche Grüße

[dispy]

Zitat:

Zitat von marabu

Hallo dispy,

lass mal den Range-Check für die Spalte "timestamp" in der WHERE-Klausel weg - da stehen blödsinnige Werte drin, wenn ich das richtig sehe.

Freundliche Grüße

Moin,
ja, so sind die blödsinnig. Aber im späteren Einsatz kommen da vernünftige Werte rein.

MFG
dispy

[omata]

Neuer Versuch...

SQL-Code:

SELECT timestamp AS time,
       new_owner AS new_owner_id
FROM de12_conquer 
WHERE (timestamp > 0 && timestamp < 1213647029734) 
  AND (   new_owner IN (SELECT id
                        FROM de12_tribe
                        WHERE ally = 37038)
       OR old_owner IN (SELECT id
                        FROM de12_tribe
                        WHERE ally = 37038))
ORDER BY timestamp DESC LIMIT 50

oder so...

SQL-Code:

SELECT timestamp AS time,
       new_owner AS new_owner_id
FROM de12_conquer c
WHERE (timestamp > 0 && timestamp < 1213647029734) 
  AND EXISTS (SELECT *
              FROM de12_tribe
              WHERE ally = 37038
                AND (   id = c.new_owner 
                     OR id = c.old_owner))
ORDER BY timestamp DESC LIMIT 50

Wieviel Zeilen ergibt das Folgende überhaupt?

SQL-Code:

SELECT id
FROM de12_tribe
WHERE ally = 37038

Geht es schneller ohne den timestamp und die Sortierung?

[dispy]

Zitat:

Wieviel Zeilen ergibt das Folgende überhaupt?

nen leeres Resultat - die ID hat sich geändert.
MIt der aktuellen ID genau 48.



1ter query: läuft ewig
2ter Query: auch ewig

Zitat:

Geht es schneller ohne den timestamp und die Sortierung?

Ohne geht es auch nicht von daher is das schnurzpipsegal.Sollte aber logischwerweise länger brauchen.

MFG
dispy