Compter le nombre de messages non lus

**Mika56** · 14/09/2012, 01h37

Salut,

Le titre parait simple mais il n'y a pas beaucoup de place

J'ai donc un forum, avec un système de lu/non lu. Individuellement (sur chaque sujet), le système fonctionne bien.
Le soucis, c'est que je souhaite qu'un utilisateur puisse savoir s'il a des messages non-lus depuis l'accueil, et ce, pour chaque forum.
Pour corser la chose, l'utilisateur a un lien "Marquer tous les forums comme lus"; lorsqu'il appuie dessus, sa "date de dernière lecture de tout le forum" est mise à jour dans la base de données.

Pour compter le nombre de sujets non-lus depuis cette dernière date (enfin, savoir qu'il y en a au moins un me suffit), je suis partit du principe que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

nonVus = ("Nombre de topics avec un message plus récent que la date" - "Nombre de topics lus dont le dernier message est plus récent que la date"

J'en ai déduit la requête SQL suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT f.id, f.nom, f.description, f.niv_acces, f.parent, f.totalTopics, f.totalPosts, f.lastPostId, f.`order`,
	((
		SELECT COUNT(t.id)
		FROM topics t
		WHERE (SELECT p.date FROM posts p WHERE p.id = t.lastPostId) > "2011-09-13 23:32:30"
			AND t.forumId = f.id
	) - (
		SELECT COUNT(t.id)
		FROM topics t
		LEFT JOIN vues v ON t.id = v.id_topic
		WHERE (SELECT p.date FROM posts p WHERE p.id = t.lastPostId) > "2011-09-13 23:32:30"
			AND v.id_lastpost = t.lastPostId
			AND v.id_m = 479
			AND t.forumId = f.id
	)) AS nonlus
FROM forums f
ORDER BY f.`order`, f.`id`;

(Exemple pour l'utilisateur N° 479 qui a marqué tous les forums comme lus le 13 septembre 2011 à 23h32:30)
Le problème, c'est qu'elle n'est pas optimisée du tout, 0.306s contre 0.001s sans le nonlus.

Ma base de données ressemble à cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE `forums` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `parent` mediumint(8) DEFAULT NULL,
  `lastPostId` mediumint(8) DEFAULT NULL,
  `order` tinyint(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;
CREATE TABLE `posts` (
  `id` mediumint(8) NOT NULL AUTO_INCREMENT,
  `topicId` mediumint(8) NOT NULL,
  `forumId` mediumint(8) NOT NULL,
  `titre` varchar(73) NOT NULL,
  `auteur` mediumint(8) NOT NULL,
  `date` datetime NOT NULL,
  `texte` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26940 DEFAULT CHARSET=latin1;
 
CREATE TABLE `topics` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `forumId` mediumint(8) unsigned NOT NULL,
  `titre` varchar(70) NOT NULL,
  `sousTitre` varchar(70) NOT NULL,
  `firstPostID` mediumint(8) unsigned NOT NULL,
  `lastPostId` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2273 DEFAULT CHARSET=latin1;
 
CREATE TABLE `utilisateurs` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `pseudo` varchar(25) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `readAllDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2170 DEFAULT CHARSET=latin1;
 
CREATE TABLE `vues` (
  `id_m` mediumint(8) NOT NULL,
  `id_topic` mediumint(8) NOT NULL,
  `id_forum` mediumint(8) NOT NULL,
  `id_lastpost` mediumint(8) NOT NULL,
  PRIMARY KEY (`id_m`,`id_topic`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

J'ai simplifié la table pour qu'elle ne contienne en principe que ce qui est nécessaire. Si quelque chose n'est pas clair pour vous dans la table, je me ferais un plaisir de vous aider (puis vous m'aidez

)

Du coup, je me demande si vous avez des idées pour optimiser, ou d'autres manières de faire que la mienne.

Mika.

**ericd69** · 14/09/2012, 06h27

salut,

déjà les clés étrangères? on connait pas?

ensuite tu construis une arborescence... donc un forum contient des topics qui contient des posts...

donc pas la peine de multiplier les clés étrangères genre forumId de partout par exemple...

les clés étrangères sont automatiquement indexées... 16ko mini en innodb par index...

ensuite tu as rarement plus de 255 forum... tinyint ça suffit

utilise des unsigned pour avoir des nombres non signés et ne pas perdre la moitié des valeurs possibles... ça sert à rien de préciser le nombre entre ( ) pour les entiers, c'est juste le nombre de chiffres significatifs...

de nos jours tu peux quand même utiliser l'utf8 au lieu du très limité latin1... tu peux mixer toutes les langues comme ça...

on colle toujours les valeur numériques et surtout les index en premier pour accélérer leur accès...

tes tables pourraient devenir ça:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE `forums` (
	`id`				tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
	`parent`			tinyint UNSIGNED DEFAULT NULL,
	`order`			tinyint UNSIGNED not null DEFAULT 0,
	`nom`				varchar(50) NOT NULL,
	`description`	varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `topics` (
	`id`			int UNSIGNED NOT NULL AUTO_INCREMENT,
	`forumId`	tinyint UNSIGNED NOT NULL,
	`titre`		varchar(70) NOT NULL,
	`sousTitre` varchar(70) NOT NULL,
	constraint pk_topics PRIMARY KEY (`id`)
	constraint fk_topics_forum	foreign key(`forumId`)	references `forums`(`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `posts` (
	`id`			int UNSIGNED NOT NULL AUTO_INCREMENT,
	`topicId`	int UNSIGNED NOT NULL,
	`auteur`		mediumint UNSIGNED NOT NULL,
	`titre`		varchar(73) NOT NULL,
	`date`		datetime NOT NULL,
	`texte`		text NOT NULL,
	constraint pk_posts PRIMARY KEY (`id`),
	constraint fk_posts_topic	foreign key(`topicId`)	references `topics`(`id`),
	constraint fk_posts_auteur	foreign key(`auteur`)	references `utilisateurs`(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `lu` (
  `id`					bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_utilisateur`	mediumint(8) NOT NULL,
  `id_post`				int NOT NULL,
  `date`					datetime DEFAULT NULL,
  constraint pk_lu PRIMARY KEY (`id`)
  constraint fk_lu_post		foreign key(`id_post`)			references `posts`(`id`),
  constraint fk_lu_auteur	foreign key(`id_utilisateur`)	references `utilisateurs`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `utilisateurs` (
	`id`			mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
	`pseudo`		varchar(25) NOT NULL,
	`password`	varchar(255) NOT NULL,
	`email`		varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

après la plupart des choses s'obtiennent donc par des jointures
de manière plus simple, pas besoin d'avoir plein de références à mettre à jour partout...

**Fred_34** · 14/09/2012, 12h34

Si elle est correcte, cette requête devrait être plus rapide :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT f.id, f.nom, f.description, f.niv_acces, f.parent, f.totalTopics, f.totalPosts, f.lastPostId, f.`order`, COALESCE(NB_TOPIC_NEWER_RESET,0)- COALESCE(NB_TOPIC_VIEWED_AFTER_RESET,0) AS nonlus
FROM forums f
LEFT OUTER JOIN 
(
	SELECT p.forumId,count(DISTINCT p.topicId) AS NB_TOPIC_NEWER_RESET
	FROM posts p
	WHERE p.date > "2011-09-13 23:32:30"
	GROUP BY p.forumId
) tmp1
ON f.id = tmp1.forumId
LEFT OUTER JOIN 
(
	SELECT v.id_forum,count(DISTINCT v.id_topic) AS NB_TOPIC_VIEWED_AFTER_RESET
	FROM vues v
	INNER JOIN posts p
    ON p.id = v.id_lastpost
	WHERE v.id_m = 479 AND p.date > "2011-09-13 23:32:30"
	GROUP BY v.id_forum
) tmp2
ON f.id = tmp2.id_forum
 
ORDER BY f.`order`, f.`id`;

A défaut de mettre des clés étrangères, met au moins des index dessus.

@Ericd69 Dans la table "lu" il faut surement remplacer id_post par id_topic. Ou pas ?

**Mika56** · 14/09/2012, 15h05

Envoyé par ericd69

salut,

déjà les clés étrangères? on connait pas?

ensuite tu construis une arborescence... donc un forum contient des topics qui contient des posts...

donc pas la peine de multiplier les clés étrangères genre forumId de partout par exemple...

les clés étrangères sont automatiquement indexées... 16ko mini en innodb par index...

ensuite tu as rarement plus de 255 forum... tinyint ça suffit

utilise des unsigned pour avoir des nombres non signés et ne pas perdre la moitié des valeurs possibles... ça sert à rien de préciser le nombre entre ( ) pour les entiers, c'est juste le nombre de chiffres significatifs...

de nos jours tu peux quand même utiliser l'utf8 au lieu du très limité latin1... tu peux mixer toutes les langues comme ça...

on colle toujours les valeur numériques et surtout les index en premier pour accélérer leur accès...

après la plupart des choses s'obtiennent donc par des jointures
de manière plus simple, pas besoin d'avoir plein de références à mettre à jour partout...

Salut,

Non, je ne connais pas trop les clés étrangères... Du moins, je n'ai pas l'habitude de les utiliser, j'imagine que ça permet une bonne intégrité des données ?
En suivant tes conseils j'ai donc adapté les champs et les clés étrangères à toute ma base, j'espère ne pas avoir de bêtises

Du coup, ma requête ne prends plus que 0.054s (voir 0.013s, MySQL a son cache) ! C'est encore élevé, mais bien plus acceptable

Envoyé par Fred_34

Si elle est correcte, cette requête devrait être plus rapide :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT f.id, f.nom, f.description, f.niv_acces, f.parent, f.totalTopics, f.totalPosts, f.lastPostId, f.`order`, COALESCE(NB_TOPIC_NEWER_RESET,0)- COALESCE(NB_TOPIC_VIEWED_AFTER_RESET,0) AS nonlus
FROM forums f
LEFT OUTER JOIN 
(
	SELECT p.forumId,count(DISTINCT p.topicId) AS NB_TOPIC_NEWER_RESET
	FROM posts p
	WHERE p.date > "2011-09-13 23:32:30"
	GROUP BY p.forumId
) tmp1
ON f.id = tmp1.forumId
LEFT OUTER JOIN 
(
	SELECT v.id_forum,count(DISTINCT v.id_topic) AS NB_TOPIC_VIEWED_AFTER_RESET
	FROM vues v
	INNER JOIN posts p
    ON p.id = v.id_lastpost
	WHERE v.id_m = 479 AND p.date > "2011-09-13 23:32:30"
	GROUP BY v.id_forum
) tmp2
ON f.id = tmp2.id_forum
 
ORDER BY f.`order`, f.`id`;

A défaut de mettre des clés étrangères, met au moins des index dessus.

@Ericd69 Dans la table "lu" il faut surement remplacer id_post par id_topic. Ou pas ?

Puisque j'ai suivit les conseils de Ericd69, je n'ai plus la champ forumId, ta requête ne fonctionne donc plus

Mika.

**ericd69** · 14/09/2012, 17h46

s'il y a un seul post non lu dans le topic, le topic est non lu...

après le parcours des forums est récursif... et idéalement fait en procédural avec mysql vu que mysql ne connait pas les requêtes récursives...

le forum courant est il non lu?

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select p.`topicId` as topic, sum(if(l.`date`,0,1))>0 as nonlu
from posts p
inner join `topics` t on p.`topicId`=t.`id` and t.`forumId`=@forum
left join `lu` l on l.`id_post`=p.`id` and l.`id_utilisateur`=@utilisateur
where `auteur`<>@utilisateur
group by p.`topicId`

avec @forum et @utilisateur à remplacer par les valeurs voulue ou rajouter une jointure si besoin pour l'un ou l'autre...

tu vois simplissime...

Compter le nombre de messages non lus

Requêtes MySQL

Discussions similaires

Partager

Partager