MySql - realizzazione di una query abbastanza ostica

[markzzz]

Salve,

stò creando una sorta di Forum personalizzato. Lo scenario è diviso così :

Il forum ha varie categorie; ogni categoria può contenere vari topics; ogni topic contiene 1 o più messaggi; inoltre ho una tabella dove c'è la coppia utente/topic id per tenere traccia dei topic visitati dagli utenti. Traducendo il tutto in MySql, queste sono le tabelle :

    CREATE TABLE IF NOT EXISTS `forum_categories` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `title` varchar(255) NOT NULL,
      `description` varchar(255) NOT NULL,
      `date` datetime NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS `forum_topics` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `category_id` int(11) unsigned NOT NULL,
      `title` varchar(255) NOT NULL,
      `author` varchar(255) NOT NULL,
      `date` datetime NOT NULL,
      `view` int(11) unsigned NOT NULL default '0',
      `sticky` tinyint(11) unsigned NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;    
    
    CREATE TABLE IF NOT EXISTS `forum_messages` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `topic_id` int(11) unsigned NOT NULL,
      `author` varchar(255) NOT NULL,
      `message` mediumtext NOT NULL,
      `date` datetime NOT NULL,
      `original` tinyint(11) unsigned NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS `forum_visits` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `topic` int(11) unsigned NOT NULL,
      `user` varchar(255) NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `forum_visits_unique_idx` (`topic`,`user`)
    ) ENGINE=MyISAM AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

Per ora ho scritto questa query :

    SELECT forum_categories.id, forum_categories.title, forum_categories.description, forum_categories.title, 
    COUNT(DISTINCT forum_topics.id) AS total_topics, SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies, 
    forum_messages.author, MAX(forum_messages.date) AS last_message, SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) to_view
    FROM forum_categories
    LEFT OUTER JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
    LEFT OUTER JOIN (SELECT topic, user FROM forum_visits WHERE user='"userA') r ON forum_topics.id=r.topic
    LEFT OUTER JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
    GROUP BY forum_categories.id 
    ORDER BY forum_categories.date

ma sfortunatamente non mi ritorna tutto quello che mi serve, ovvero :


1. titolo per ogni categoria; al momento funziona
2. il numero di topic per ogni categoria; al momento funziona
3. il numero, per ogni categoria, dei messaggi per ogni topic (deve però fare il conteggio solo dei messaggi in forum_messages che hanno il campo original=0); al momento funziona
4. un flag che mi dice se un determinato utente (nell'esempio userA) ha già letto il topic; da come l'ho strutturata, se è maggiore a 0 dovrà ancora leggerlo; al momento funziona
5. l'autore e la data (e quì c'è il mio problema) dell'ultimo messaggio inserito per una determinata categoria (l'ultima data me la ritorna, ma non include il corrispondente author; ne prende uno a caso);
6. avere la query più perfomante possibile;

Spero possiate darmi una mano

[markzzz]

EDIT : magari per accelerare il tutto si potrebbe evitare di contare le righe con original=1 per quanto riguarda il conteggio dei replies, ma semplicemente scartare la riga dove il topic e il messaggio hanno la stessa data (ovvero il messaggio inserito alla creazione del topic, ergo non una reply).

[zamu91]

SELECT forum_categories.id, forum_categories.title, forum_categories.description, forum_categories.title,
COUNT(DISTINCT forum_topics.id) AS total_topics, SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies,
(select forum_messages.author from messages order by (chiave che identifica l'ultimo record ) desc limit 1 ), MAX(forum_messages.date) AS last_message, SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) to_view
FROM forum_categories
LEFT OUTER JOIN forum_topics ON forum_topics.category_id=forum_categories.id
LEFT OUTER JOIN (SELECT topic, user FROM forum_visits WHERE user='"userA') r ON forum_topics.id=r.topic
LEFT OUTER JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
GROUP BY forum_categories.id
ORDER BY forum_categories.date


Prova con questa query sistemando i campi nel select. Dovrebbe andare.

[markzzz]

NO, non và! Difatti seleziona l'ultimo utente, si, ma deve selezionare l'ultimo utente di ogni categoria, non in assoluto

Chissà quel boss di nicola75ss dov'è andato a finire

[deleted_29]

Originariamente inviato da markzzz
NO, non và! Difatti seleziona l'ultimo utente, si, ma deve selezionare l'ultimo utente di ogni categoria, non in assoluto

Chissà quel boss di nicola75ss dov'è andato a finire

Io non mi intendo molto di questi problemi ( ) ma cominciamo per ordine

1) niente myisam per un forum
ENGINE=MyISAM

2) niente utf8 per un forum
DEFAULT CHARSET=utf8;

Riguardo al resto
1) niente querone per un forum
2) se vuoi tenere conteggi... tieni una tabella conteggi ed aggiornala, o da applicazione, o da trigger.
3) de-normalizza l'archivio, e tieni i titoli nelle altre tabelle. Trigger di allineamento nel caso di modifiche. Togli quindi tutti i join che puoi (possibilmente... tutti)
4) tira via tutto il possibile: group, order by, etc

I miei (probabilmente inutili) suggerimenti li ho maturati amministrando un forum con 500.000 utenti ed un traffico [ammesso che siano statistiche attendibili, ne dubito parecchio ovviamente] circa pari a questo

La versione breve è: semplice-semplice-semplice; veloce-veloce-veloce

[markzzz]

Grazie per i consigli. Però avrei delle domande :

1) perchè niente MyIsam? Così giusto per curiosità. In ogni caso il mio host provider non supporta InnoDB, quindi dovrò tenermelo;

2) utf8 mi serve per gestire caratteri da ogni parte del mondo, sicuro sia così "pesante"?

3) ci pensavo anche io effettivamente che il querone fosse meno veloce di tante query, anche se aumentano vertiginosamente le connessioni al db : es. per ricavare i dettagli di una determinata categoria ne servono :

1 generale per le informazioni basilari (titolo, descrizione);
1 per vedere quanti topic contiene;
1 per vedere quanti replies per ogni categoria;
1 per andare a vedere di chi è l'ultimo messaggio;

Siamo già a 4 query; se le categorie sono 5 sono 20 query; 20 al posto di 5... sicuro che convenga?

4) intendi per esempio una tabella forum_topics_count che ha per esempio (id, topic_id, n_reply, n_visits)? (poi idem per le categories...)

5) cosa intendi per "de-normalizza l'archivio, e tieni i titoli nelle altre tabelle"?

Grazie ancora, aspetto risposte

[deleted_29]

Originariamente inviato da markzzz
Grazie per i consigli. Però avrei delle domande :

1) perchè niente MyIsam? Così giusto per curiosità. In ogni caso il mio host provider non supporta InnoDB, quindi dovrò tenermelo;

il livello di concorrenza di myisam è pessimo nel caso di un mix di letture e scritture (come capita nei forum).
Ogni scrittura blocca totalmente l'accesso (anche in lettura) alle tabelle (*eccezione: configurazione per accodamento, ma poco significativo nel tuo caso).
Inoltre non puoi fare backup "hot", ossia senza bloccare totalmente il forum (già di per sè questo è un motivo esimente)
Nel caso di caduta del server devi lanciare "a mano" la correzione sulle tabelle, le quali a loro volta sono facili alla corruzione, e lente in fase di ricostruzione

2) utf8 mi serve per gestire caratteri da ogni parte del mondo, sicuro sia così "pesante"?

E' un dato di fatto oggettivo, non un'opinione

3) ci pensavo anche io effettivamente che il querone fosse meno veloce di tante query, anche se aumentano vertiginosamente le connessioni al db : es. per ricavare i dettagli di una determinata categoria ne servono :

1 generale per le informazioni basilari (titolo, descrizione);
1 per vedere quanti topic contiene;
1 per vedere quanti replies per ogni categoria;
1 per andare a vedere di chi è l'ultimo messaggio;

Ecco un esempio di come NON fare.
O meglio di come fare per forum piccoli.
Nel tuo caso devi avere una singola query, non N query diverse.
Devi evitare tutte le count(*), le quali sono lentissime (*unica eccezione: count(*) senza where per tabelle myisam)
Non devi andare a vedere chi è l'ultimo messaggio.
Nè contare quante risposte e così via.

Siamo già a 4 query; se le categorie sono 5 sono 20 query; 20 al posto di 5... sicuro che convenga?

Non ti so dire, non sono così esperto

4) intendi per esempio una tabella forum_topics_count che ha per esempio (id, topic_id, n_reply, n_visits)? (poi idem per le categories...)

Ovviamente

5) cosa intendi per "de-normalizza l'archivio, e tieni i titoli nelle altre tabelle"?

Grazie ancora, aspetto risposte

significa che un archivio normalizzato è il modo migliore per avere BASSE prestazioni, soprattutto nel caso di tabelle myisam, soprattutto nel caso di mix letture-scritture.
Nel tuo schema (del quale in realtà non mi sono molto interessato) il titolo della "categoria" lo puoi benissimo metterlo pure dentro i topic, nel caso in cui ti interessi (*dipende dall'uso)
E così per altre informazioni.

Non è tanto la "dimensione" del database ad essere oggi importante, quanto la responsività, e quest'ultima la raggiungi "slegando" il più possibile le query tra di loro (*rendendole meno interdipendenti, quindi meno lock) e, soprattutto, mantenendole al minimo sia come complessità, che come tempi.

---
Poi se il tuo forum avrà 5000 post, e 50 utenti, puoi fare come vuoi, non rileverai concrete differenze.
Il "problema" si pone quando gli incrementi polinomiali nei tempi iniziano a "pesare", e da lì in poi ogni piccolo aumento corrisponde ad un peggioramento sensibile.

E' infatti normale avere forum che funzionano benissimo, anche per anni, per poi "improvvisamente" andare in crisi superata una certa soglia.
---
Bon, questi sono i miei consigli, nulla di che

[markzzz]

Originariamente inviato da franzauker
Ecco un esempio di come NON fare.
O meglio di come fare per forum piccoli.
Nel tuo caso devi avere una singola query, non N query diverse.
Devi evitare tutte le count(*), le quali sono lentissime (*unica eccezione: count(*) senza where per tabelle myisam)
Non devi andare a vedere chi è l'ultimo messaggio.
Nè contare quante risposte e così via.

ma come? Mi hai detto niente querone....
Forse ti riferisci al fatto di applicare una singola query su tabelle organizzate diversamente

Effettivamente, solo il fatto di fare il join tra forum_categories e forum_categories_counter (join molto minimo) ottengo con una singola query le stesse informazioni. (dove posso includere il numero di quanti messaggi/risposte ho, solamente tenendo aggiornata la tabella counter).

Oppure potrei aggiungere semplicemente due campi (total_topics, total_messages) nella tabella categories, evitando un ulteriore join).

Il problema resta quello dell'ultimo messaggio... quello è totalmente sconnesso dalla logica di parentela tra categorie/topics/messaggi

[deleted_29]

Originariamente inviato da markzzz
ma come? Mi hai detto niente querone....

Eh comunque ultimo messaggio e quante risposte le voglio presenti hehe

te l'ho già scritto sopra.

"quante risposte" e "ultimo messaggio" lo puoi benissimo aggiornare

1) da applicazione. la tua applicazione sa quando viene fatta una nuova risposta=> può aggiornare il totale. Analogamente sa chi è l'ultimo a postare.

2) da db (da trigger). quando ne viene aggiunta => aggiorna il totale/ultimo utente.

Non ha il minimo senso eseguire milioni di volte la stessa query per fare conteggi o valutazioni "statistiche" che GIA' sai (o potresti sapere).

EDIT: inoltre valuta bene (nel caso di myisam direi che è indispensabile) "dove" memorizzare le informazioni. Tutto questo scrivere, infatti, blocca le relative tabelle.
Ecco quindi perchè, ad esempio, non ti conviene mettere "ultimo autore" dentro una tabella "principale", bensì crearne una collegata (linkata con un join, seppure a malincuore), per aggiornare LI' i conteggi.

Altrimenti ad ogni aggiornamento blocchi, praticamente, l'intero forum.

Meglio fare un upgrade dello spazio e mettere innodb

E, per inciso, evita i campi NULL (soprattutto con mysql) giacchè rallentano un po' tutto.
Anzi meglio ancora mettili NOT NULL direttamente nella definizione.

[markzzz]

Effettivamente hai ragione anche te.

La query si è ridotta a :

SELECT title, description, topics, messages, last_date, last_user FROM forum_categories ORDER BY id ASC

Non male Grazie per i succulenti consigli. Unica cosa è utf8 e mysam dovrò tenermeli, ma già ho risolto parecchio dal mio punto di vista!