MYSQL Abfrage, Vergleich täglicher Daten

[TS-JC]

Hallo zusammen

also ich scripte Statistiken für ein Browsergame.
Da hat jeder ne Insel.

Jeden Tag speicher ich in einer Tabelle sämtliche Inseln mit Besitzer und dem aktuellen Datum.
Nun will ich alle angezeigt bekommen bei denen sich von gestern auf heute der Besitzer geändert hat.

Habe es nun mit 2 Abfragen gemacht.
Erstmal alle von heute holen und dann diese einzelnd durchgehen und eine 2. Abfrage guckt ob sich was zu gestern verändert hat.

Bei 3000 Inseln ist dies aber recht langsam und es könnten ja noch mehr werden.

Hat vielleicht jemand eine Idee wie ich das in einer Abfrage lösen kann?

 

[zeja]

So sollte es gehen:

SELECT a.island, a.owner currentOwner, b.owner oldOwner, a.date
FROM inseln a LEFT JOIN inseln b
     ON a.island = b.island AND a.date = CURDATE() AND a.date > b.date
WHERE b.owner IS NOT NULL AND DATE_SUB(a.date,INTERVAL 1 DAY) <= b.date;

Man sucht sich aus der Tabelle alle Datensätze deren aktuelles Besitzerdatum dem aktuellen entspricht und kombiniert diese mit allen Datensätzen deren Besitzerdatum schon länger zurückliegt. Dann guckt man wo sich das Besitzerdatum um nur einen Tag unterscheidet.

 

[TS-JC]

So habs nun endlich geschafft mir die Sache anzugucken.
Liefert mir genau die anderen Datensätze zurück, also alle wo nix passiert ist.

Habe bissle mit rumgespielt, aber es leider nicht geschafft dies umzukehren.
Naja, ich versuchs weiter, aver evt kann mir ja jemand helfen

thx jedenfalls nochmal, der Ansatz scheint ja richtig zu sein

 

[zeja]

Hmm poste nochmal ein wenig von deinen Daten. Bei ner Tabelle die ich mir selber in etwa so wie dus hast angelegt hatte funktionierte meine Abfrage. Kann das selber nich ausm Kopf, brauche also Daten um selber ein wenig rumprobieren zu können.

 

[TS-JC]

WHERE b.owner IS NOT NULL
war falsch

funktionieren tuts mit
WHERE a.owner != b.owner

thx trotzdem

 

[TS-JC]

So habe das Script irgendwie damals verschlampt und nu wieder gefunden.
Bei ~164000 Datensätzen hängt sich mein phpMyAdmin auf wenn ich die ausführe
Naja, es hängt sich nicht wirklich auf, es braucht 440sek bis es fertig ist, dann kommen aber scheinbar die richtigen Ergebnisse raus *g*

Wie auch immer, das ist viel zu lang.
Vielleicht hat ja jemand eine Idee das schneller zu bekommen.

Hier mal meine Abfrage:

SELECT a.isleid, a.memberid currentOwner, b.memberid oldOwner, a.datum FROM inselstats_ihistory a LEFT JOIN inselstats_ihistory b ON a.isleid = b.isleid AND a.datum = CURDATE() AND a.datum > b.datum WHERE a.memberid != b.memberid AND a.isleid = b.isleid AND DATE_SUB(a.datum,INTERVAL 1 DAY) <= b.datum

Und hier ein mySQL Dump mit Testdaten:

CREATE TABLE `inselstats_ihistory` (
  `id` int(11) NOT NULL auto_increment,
  `isleid` int(11) NOT NULL default '0',
  `memberid` int(11) NOT NULL default '0',
  `punkte` int(11) NOT NULL default '0',
  `datum` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `inselstats_ihistory` VALUES (1, 1, 1, 0, '2006-12-19');
INSERT INTO `inselstats_ihistory` VALUES (2, 2, 2, 2, '2006-12-19');
INSERT INTO `inselstats_ihistory` VALUES (3, 3, 3, 3, '2006-12-19');
INSERT INTO `inselstats_ihistory` VALUES (4, 1, 2, 0, '2006-12-20');
INSERT INTO `inselstats_ihistory` VALUES (5, 2, 2, 0, '2006-12-20');
INSERT INTO `inselstats_ihistory` VALUES (6, 3, 3, 0, '2006-12-20');

Alternative hier meine 164k Datensätze:

 

[zeja]

Wenn du so viele Daten hast solltest du eine schnellere Datenbank nutzen die dafür ausgelegt ist (Oracle oder so etwas) oder eine temporäre Tabelle machen in der du einfach die Benutzerwechsel immer einträgst und nach einem Tag wieder löscht. Sprich eine Tabelle die das Ergebnis der Anfrage schon immer laufend enthält.

 

[TS-JC]

Ich dachte immer mySQL wäre auch für große Datenmenge ausgelegt.

Eine Tabelle mit den Wechseln kommt leider nciht in frage, da ich eigentlich eine Funktion einbauen wollte wo man einfach 2 Tage angibt und dann die Ergebnisse bekommt.