✔ Sortieren und Group BY

[lisali]

Es ist nur diese Tabelle:

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `title` varchar(60) NOT NULL default 'Kein Betreff',
  `text` text NOT NULL,
  `status` tinyint(1) NOT NULL default '0',
  `date` datetime NOT NULL,
  `uid_delete` tinyint(1) NOT NULL default '0',
  `sid_delete` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=210 ;


INSERT INTO `messages` (`id`, `sid`, `uid`, `title`, `text`, `status`, `date`, `uid_delete`, `sid_delete`) VALUES
(161, 1, 45, 'Kein Betreff', '166', 1, '2010-05-24 21:38:06', 0, 0),
(186, 27, 1, 'Kein Betreff', 'adsasd', 1, '2010-05-27 21:50:41', 0, 0),
(200, 1, 1, 'Kein Betreff', 'test', 1, '2010-05-30 10:10:40', 1, 0),
(201, 1, 1, 'Kein Betreff', 'aaaa', 1, '2010-05-30 10:13:08', 1, 0),
(202, 1, 1, 'Kein Betreff', 'ey', 1, '2010-05-30 10:54:24', 1, 0),
(203, 1, 1, 'Kein Betreff', 'ye', 1, '2010-05-30 10:54:35', 1, 0),
(204, 1, 1, 'Kein Betreff', 'yey', 1, '2010-05-30 10:55:28', 1, 0),
(205, 1, 1, 'Kein Betreff', 'yey', 1, '2010-05-30 10:56:10', 1, 0),
(206, 1, 1, 'Kein Betreff', 'test', 1, '2010-05-30 11:04:35', 1, 0),
(208, 1, 1, 'Kein Betreff', 'uuk', 1, '2010-05-30 11:44:42', 1, 0),
(209, 1, 1, 'Kein Betreff', 'ujujjujujuuju', 0, '2010-05-30 11:44:46', 0, 0);

 

[lisali]

Zu Testzwecken sollte man vielleicht die uid und sid etwas umändern.

 

[CPoly]

Eben beim Staubsaugen kam mir die Lösung :-D

Man nehme das Query aus deinem Startpost und entferne das GROUP BY. Anschließend ersetzt man den kompletten WHERE Teil durch folgendes

WHERE m.`id` IN (SELECT MAX(`id`) FROM messages WHERE `uid`='".$_SESSION['id']."' AND `uid_delete`='0' GROUP BY `sid`)

 

[lisali]

Wow, perfekt... dankeschön!

Danke!

 

[bn]

Hallo lisali,

auf den ersten Blick mag die Abfrage perfekt sein, da sie funktioniert. Allerdings läuft die Abfrage nicht wirklich optimal. Ich habe mir mal die Arbeit gemacht und ein Testdatenscript zu deinem Problem geschrieben. Das Script generiert 10.000 zufällige Benutzer und 500.000 zufällige Nachrichten dieser Benutzer. Auf die entstandenen Testdaten habe ich den Query anschließend abgeschossen und meine Vermutung hatte sich bestätigt. Der Query saugt erhebliche Performance und läuft eine Ewigkeit. Mit wenigen Handgriffen kann aber Abhilfe geschaffen werden.

Doch nun mal langsam - zunächst das Testdatenscript:

<?
// nicht auf einem produktiven System ausführen!
$con = mysql_connect ("localhost", "root", "xxxxxxxxxxx");
mysql_select_db ("test");

$signs = "abcdefghijklmnopqrstuvwxyz";
function getRandUsername ()
{
	global $signs;
	
	return str_shuffle ($signs);
}

function getRandText ($repeat)
{
	global $signs;
	
	return str_repeat (str_shuffle ($signs) . " ", $repeat);
}

// 10.000 Benutzer anlegen
for ($i=0; $i<10000; $i++)
{
	mysql_query ("INSERT INTO users (username) VALUES ('".getRandUsername ()."')");
}

// 500.000 Messages anlegen
$minDateTs = strtotime ("2009-01-01");
$maxDateTs = strtotime ("2010-05-30 23:59:59");
$stepTs	   = floor (($maxDateTs - $minDateTs) / 500000);

for ($i=0; $i<500000; $i++)
{
	$sid 	= rand (1, 10000);
	$uid 	= rand (1, 10000);
	$title 	= getRandText (3);
	$text 	= getRandText (30);
	$status = rand (0, 1);
	$date 	= strftime ("%Y-%m-%d %T", $minDateTs + $stepTs*$i);
	
	$uid_delete = rand (0, 1);
	$sid_delete = rand (0, 1);
	
	mysql_query ("
		INSERT INTO 	messages 
		(				sid, 
						uid, 
						title, 
						text, 
						status, 
						date,
						uid_delete,
						sid_delete
		) VALUES (		'$sid', 
						'$uid', 
						'$title', 
						'$text', 
						'$status', 
						'$date',
						'$uid_delete',
						'$sid_delete'
		)
	");
}
?>

Nun wo einige Minuten ins Land gestrichen sind und die Testdaten zur Verfügung stehen, machen wir uns mal an die Optimierung des Ausgangs-Queries. Ich habe die Parameter der Abfrage durch starre Werte erstetzt (uid=1).

SELECT 		m.id AS message_id, 
			m.text, 
			m.sid, 
			m.status,
			u.username AS name,
			DATE_FORMAT(m.date,'%d.%m.%Y, %H:%i') AS shortdate, 
			TIMESTAMPDIFF(SECOND, m.date, NOW()) AS sec
FROM 		messages AS m 
LEFT JOIN 	users AS u 
			ON u.id = m.sid
WHERE 		m.id IN 
			(
				SELECT 		MAX(id) 
				FROM 		messages 
				WHERE 		uid='1' 
							AND uid_delete='0' 
				GROUP BY 	sid
			)
ORDER BY 	m.id DESC

Ich empfehle dir nicht, diesen Query auf die Testdaten los zu lassen. Ich habe die laufende Abfrage nach 10 Minuten erfolglos abgebrochen. Stellen wir dem Query ein EXPLAIN voran, erhalten wir folgende Ausgabe:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 500011
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.m.sid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: messages
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 500011
        Extra: Using where; Using temporary; Using filesort

Ich habe die markanten Stellen rot markiert. (Diese Ausgabe erreicht man in der MySQl Konsole übrigens mit \G.) Wie wir sehen, werden bei Reihe 1 und 3 keinerlei Indexe verwendet (key) bzw. gar zur Verwendung angeboten (possible_keys). Das liegt daran, dass keine treffenden Indexe vorhanden sind. Schauen wir uns also die Abfrage an - insbesondere jedoch zunächst diesen Teil:

			(
				SELECT 		MAX(id) 
				FROM 		messages 
				WHERE 		uid='1' 
							AND uid_delete='0' 
				GROUP BY 	sid
			)

Es werden die Felder uid und uid_delete in der WHERE-Bedingung abgefragt. ein zusammengesetzter Index bestehend aus uid und uid_delete solle also helfen:

ALTER TABLE messages ADD INDEX idx_uid_uid_delete(uid, uid_delete);

Nun führen wir den Explain auf den unveränderten Query erneut aus und erhalten folgendes Ergebnis (kann bei dir abweichen, zwecks Zufallsgenerator):

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 500011
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.m.sid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: messages
         type: ref
possible_keys: idx_uid_uid_delete
          key: idx_uid_uid_delete
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where; Using temporary; Using filesort

Wir sehen bereits den ersten positiven Effekt - in Reihe 3 wird unser soeben angelegter Index verwendet und die Anzahl der zu betrachtenden Reihen verringert sich von 500001 auf 22. "const" ist so ziemlich das Beste, was man bei einem Index rausholen kann! Dennoch ungünstig: "Using temporary, Using filesort". Beim Blick auf die Abfrage sehen wir, dass die Spalte sid gruppiert wird. Also erweitern wir den soeben erstellen Index um diese Spalte und legen diesen 3er Index zum Test parallel an:

ALTER TABLE messages ADD INDEX idx_uid_uid_delete_sid(uid, uid_delete, sid);

Nach erneutem EXPLAIN der unveränderten Abfrage sehen wir in usnerer Optimierung folgendes Ergebnis:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 500011
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.m.sid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: messages
         type: ref
possible_keys: idx_uid_uid_delete,idx_uid_uid_delete_sid
          key: idx_uid_uid_delete_sid
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where

Wir haben erreicht, dass durch unseren neu angelegten 3er Index in Reihe 3 nun lediglich "Using where" aufschlägt und das geht völlig in Ordnung!. Der Query benötigt auf meinem Testsystem jetzt (ohne EXPLAIN) "lediglich" noch 58 Sekunden. Wir sind also noch nicht fertig. Widmen wir uns nun Reihe 1 der EXPLAIN Ausgabe - wir müssen a) die Anzahl der Reihen runter bekommen und zum anderen USING filesort los werden. Ein Blick auf die Abfrage zeigt uns, dass auf die Tabelle messages mit dem Alias m nur die WHERE Bedingung m.id IN (SUBSELECT) eingesetzt wird. Innerhalb des Subselects wird die uid gegen die uid des Benutzers mit der Session abgefragt. Es mag zwar doppelt gemoppelt erscheinen, aber wir sollten diese Bedingung auch auf die Tabelle messages mit dem Alias m anwenden. Folgender Query entsteht:

[SELECT 		m.id AS message_id, 
			m.text, 
			m.sid, 
			m.status,
			u.username AS name,
			DATE_FORMAT(m.date,'%d.%m.%Y, %H:%i') AS shortdate, 
			TIMESTAMPDIFF(SECOND, m.date, NOW()) AS sec
FROM 		messages AS m 
LEFT JOIN 	users AS u 
			ON u.id = m.sid
WHERE 		m.id IN 
			(
				SELECT 		MAX(id) 
				FROM 		messages 
				WHERE 		uid = '1' 
							AND uid_delete = '0' 
				GROUP BY 	sid
			)
			AND m.uid = 1 
			AND m.uid_delete = 0
ORDER BY 	m.id DESC

Nun schauen wir uns mittels EXPLAIn auch diesen Query an:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: m
         type: ref
possible_keys: idx_uid_uid_delete,idx_uid_uid_delete_sid
          key: idx_uid_uid_delete
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.m.sid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: messages
         type: ref
possible_keys: idx_uid_uid_delete,idx_uid_uid_delete_sid
          key: idx_uid_uid_delete_sid
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where

Hui, wir haben nun auch in Reihe 1 nur noch 22 in Frage kommende Datensätze. Unsere doppelt gemoppelte Bedingung zeigt also eine positive Wirkung. Führen wir die Abfrage nun ohne EXPLAIN aus, wird sie nun in 0.0038 Sekunden ausgeführt. Das kann sich bei dieser Datenmenge wirklich sehen lassen. Dennoch gibt es noch eine kleine Unschönheit in Reihe 1 des EXPLAIN's - Using filesort. Grund dafür ist die Sortierreihenfolge ORDER BY m.id DESC. Es wird bereits der Index idx_uid_uid_delete verwendet. Diesen gilt es nun um die Sortierspalte zu erweitern.

ALTER TABLE messages ADD INDEX idx_uid_uid_delete_id(uid, uid_delete, id);

Erneut wird die Abfrage mittels EXPLAIn an den Server gesendet - Ergebnis:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: m
         type: ref
possible_keys: idx_uid_uid_delete,idx_uid_uid_delete_sid,idx_uid_uid_delete_id
          key: idx_uid_uid_delete_id
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.m.sid
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: messages
         type: ref
possible_keys: idx_uid_uid_delete,idx_uid_uid_delete_sid,idx_uid_uid_delete_id
          key: idx_uid_uid_delete_sid
      key_len: 5
          ref: const,const
         rows: 22
        Extra: Using where

Unser neu angelegter Index idx_uid_uid_delete_id greift und "Using filesort" hat sich verabschiedet. Erneut lassen wir den Query ohne EXPLAIN laufen. Die Query benötigt nun noch 0.0038 Sekunden - mh - genauso lang wie vor dem letzten Optimierungsschritt. Zwar läuft der Query theoretisch schneller, praktisch spürt man diese Optimierung jedoch nicht. Hier würde ich dazu raten, den zuletzt angelegten Index idx_uid_uid_delete_id wieder zu entfernen, da der Nutzen dieses Indexes nicht im Verhältnis zum Aufwand steht. Letztendlich muss beim Einfügen/Bearbeiten eines neuen Datensatzes auch immer der Index geschrieben werden. Das kostet Zeit und Plattenplatz.

Räumen wir also alle unnötigen Indexe auf:

ALTER TABLE messages DROP INDEX idx_uid_uid_delete, DROP INDEX idx_uid_uid_delete_id

Wie du siehst, ist eine funktionierende Abfrage nicht gleichzeitig eine gute Abfrage. Zwar hat nicht die reine Modifikation der Abfrage zum Ziel geführt, sondern das Zusammenspielt der Modifikation und des Setzens gescheiter Indexe. Aber das ist nun mal SQL.

Und jetzt kommt noch ein Tipp - entferne aus dem Query die Datumsoperationen und er läuft schneller.

DATE_FORMAT(m.date,'%d.%m.%Y, %H:%i') AS shortdate, 
TIMESTAMPDIFF(SECOND, m.date, NOW()) AS sec
# ersetzen durch
m.date

Der größte Vorteil ist, dass der Querycache effektiver greift. Du spürst den Unterschied, wenn du die identsiche Abfrage ohne die Datumsoperationen mehrmals hintereinander abschickst. Beim 2. Aufruf holt der Server das Ergebnis aus dem Cache (0.0003 gegenüber 0.0038 Sekunden). Du kannst die Datumsoperationen auch ganz bequem mittels PHP machen! Die Spaltennamen date und text sind übrigens verbesserungswürdig. Alles was in der SQL-Notation vorkommt, sollte nicht als Spaltenname verwendet werden.

Grüße BN

 

[lisali]

Wow, ich weiß gar nicht was ich sagen soll, aber ich danke dir wirklich vielmals für die sehr mühevolle Arbeit, die du dir da für mich gemacht hast! Ich weiß gar nicht was ich sagen soll.

Leider habe ich nicht mal annähernd auch nur 100 Besucher momentan, dass ich mir darüber Gedanken machen würde, aber ich möchte natürlich auch so gut wie möglich programmieren, dass es eben auch schnell bzw. effizient ist.

Hast du denn außer bei den Datumsfunktionen noch etwas zu bemängeln am Query?

Und da hätte ich noch eine wichtige Frage. Wie könnte ich denn dieses m.`date` in PHP in reine Sekunden umwandeln? Ich habe nämlich eine selbstgeschrieben Funktion namens timediff:

// --- Zeitabstand:
function timediff($seconds) 
{
	# --- Umrechnung von Sekunden in Minuten, Stunden, Tagen, Wochen, Monaten, Jahre:
	$mins = ( $seconds / 60 % 60 );
	$hours = ( $seconds / 60 / 60 % 60 );
	$days = ( $seconds / 24 / 60 / 60 % 60 );
	$weeks = ( $seconds / 7 / 24 / 60 / 60 % 60 );
	$months = ( $seconds / 4 / 7 / 24 / 60 / 60 % 60 );
	$years = ( $seconds / 12 / 4 / 7 / 24 / 60 / 60 % 60 );
	if ($seconds <= 172800)
	{ # --- Wenn <= 2 Tage (172.800 Sekunden)
	if ($hours != 0){ $timediff .= "<strong>".$hours."</strong>h"; }
	elseif ($mins >= 1) { $timediff .= "<strong>".$mins."</strong>min"; }
	else { $timediff .= "<strong>".$seconds."</strong>sec"; }
	}
	elseif ($seconds > 172800 && $seconds < 604800) 
	{ # --- Wenn > 2 Tage (172.800 Sekunden) und < 1 Woche (604.800 Sekunden)
	if ($days != 0){ $timediff .= "<strong>".$days."</strong>d"; }
	}
	elseif ($seconds > 518400) 
	{ # --- Wenn > 6 Tage (518.400 Sekunden)
	if ($weeks <= 4){ $timediff .= "<strong>".$weeks."</strong>w"; }
	elseif ($months <= 11){ $timediff .= "<strong>".$months."</strong>mon"; }
	elseif ($months >= 12){ $timediff .= "<strong>".$years."</strong>y"; }
	}
	
	return $timediff;
}

Und damit rechne ich überall auf der Seite die Zeitdifferenzen aus. Ist es überhaupt schlau/ratsam es so zu machen oder ist das performancelastig, wenn er erstmal mit Hilfe der Sekunden rechnet?
Und wenn daran nicht zu bemängeln ist, wie könnte ich denn die Sekunden von diesem DATETIME bekommen?

Danke nochmal, BN!

 

[timestamp]

Hi

warum so aufwendig? Dafür bietet PHP doch schon Funktionen
date()
mktime()

 

[lisali]

Aber ich brauch meine Funktion ja, um die Zeitunterschiede vom Tabellenzeitpunkt zu jetzt zu ermitteln... oder gibt es da auch was?

Und wie kann ich von sql Datetime in PHP wandeln?

 

[CPoly]

Das kannst du direkt in der Abfrage mache.

SELECT (NOW()-deine_datetime_spalte) AS sekunden FROM tabelle

Es gibt in mysql noch viel mehr Möglichkeiten mit Datum und Uhrzeit zu arbeiten.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

[lisali]

Das weiß ich ja bereits und hatte ich in der Abfrage auch die ganze Zeit gemacht. Bloody Newbie hat aber einen Beitrag geschrieben, dass das Geschwindigkeit nimmt... mir gehts genau um Datetime in Php in sek.