SQL Indexing, Keys und Struktur

[omaliesschen]

Hi,

ich schreib gerade eine Chatanwendung welche die klassische PM Funktion ablösen soll.

Dabei gibt es eine Tabelle in der unter anderem die chat_id gespeichert wird.

Schickt ein Nutzer einem anderen eine Nachricht und eröffnet damit einen neuen Chatverlauf werden zwei Reihen eingefügt die weitestgehend identisch sind. Eine gehört dem Sender und eine dem Empfänger.

Löscht der Empfänger die Konversation wird "seine" Zeile gelöscht. Löscht der Sender die Konversation wird auch "seine" Zeile gelöscht.

Die Nachrichten werden in einer anderen Tabelle abgelegt.

Wie könnte man entweder die Tabelle gestalten (indexes) oder das Konzet selbst, um Full Table Lookups zu vermeiden?

CREATE TABLE IF NOT EXISTS `user_chat_parent_nodes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chat_id` int(32) NOT NULL,
  `owner_id` int(21) unsigned NOT NULL DEFAULT '0',
  `partner_id` int(8) unsigned NOT NULL DEFAULT '0',
  `recipient_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `sender_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `recipient_new` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `sender_new` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `last_update` int(21) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=45 ;


INSERT INTO `user_chat_parent_nodes` (`id`, `chat_id`, `owner_id`, `partner_id`, `recipient_deleted`, `sender_deleted`, `recipient_new`, `sender_new`, `last_update`) VALUES
(35, 2, 1, 7, 0, 0, 1, 1, 1364563504),
(36, 2, 7, 1, 0, 0, 1, 1, 1364563504),
(37, 3, 1, 7, 0, 0, 1, 1, 1364563504),
(39, 3, 7, 1, 0, 0, 1, 1, 1364563504);

$sth = $dbh->prepare('SELECT z.chat_id, z.partner_id, z.last_update, z.recipient_new, a.a_name, a.avatar
FROM user_chat_parent_nodes z
LEFT OUTER JOIN authors a
ON a.id = z.partner_id
WHERE z.owner_id = ?
AND z.recipient_deleted = 0
ORDER BY z.last_update 
DESC');


$sth->execute(array($user_id));

foreach ($sth as $r) {

$prc_chat_list[] = array(
    'chat_id'             => $r[0], 
    'chat_partner_id'     => $r[1], 
    'chat_last_action'    => get_time_ago((int)$r[2]),
    'chat_new_msg'        => $r[3],
    'chat_partner_name'   => count($r[4]) > 25 ? san(substr($r[4]), 0 ,25).'[...]' : san($r[4]), 
    'chat_partner_avatar' => san($r[5])    
    );

}

Mir kam schon der Gedanke eine Tabelle zu erstellen mit primary key auf die Nutzer id und einer Spalte mit allen chat_ids separiert durch Kommas aber dass ist wohl noch schlechter als die aktuelle Lösung?

 

[Daaron]

Ich weiß nicht... Gerade solche Chat-Geschichten sind eigentlich ein typischer Fall für eine NoSQL-Lösung, irgend einen Key-Value-Store.

Chats lösen permanent Schreiboperationen aus, nach jeder neuen Nachricht zerballert es dir den Index deiner SQL-Tabelle. Bei etwas mehr Last ist dein DBMS mehr damit beschäftigt, die Chat-Tabelle neu zu indizieren als die eigentlichen Inhalte anzuzeigen.

 

[omaliesschen]

Überleg gerade ob ich mit json files und symlinks arbeiten soll. Das würde es einfacher machen da lediglich der Dateiname und der ordner ausgelesen werden müsste.

chat-ID.json

Sinnvoll?


json Dateien oberhalb des Webroots mit Symlinks in den chatordnern der jeweiligen Nutzer.

Ab welcher Dateigröße sollte man splitten?

Vll. sogar eine komplett DB unabhängige Lösung.

 

[Hancock]

@Daaron:
Die Query-Cache ist dann im Arsch, aber für das kann man die auch deaktivieren (ermöglicht dann auch Multithreading in MySQL).

Index über
z.owner_id,z.partner_id(,z.last_update)
z.last_update
a.id

was funktioniert sagt dir explain select.

 

[omaliesschen]

Moin,

danke für die Tips. Hab mich für eine Lösung mit nodejs und websockets (sockets.io) entschieden.

Der Verlauf wird im json Format gespeichert und beim erneuten öffnen mittels Symlink eingebunden.

JSON Format. Gibts da brauchbare Seiten welche erläutern was erlaubt ist und was nicht? Newlines machen z.B. Probleme.

 

[carom]

Mit korrektem escapen sollten Umbrüche etc. keinerlei Probleme machen. Aber das solltest du eh nicht selbst übernehmen, i.d.R. gibt es doch Methoden, die das für dich Übernehmen (z.B. JSON.stringify in Javascript). Ansonsten http://www.json.org.



MongoDB und CouchDB nutzen übrigens direkt JSON als Dokumentenformat, nur so FYI.