Problème optimisation d'un count() : Mysql

[noren]

Salut

J'essaye en ce moment de bosser un peu l'optimisation à mon humble niveau et je dois avouer que je galère un peu. J'ai remplie ma base de données d'un nombre significatifs d’enregistrements qu'il s'aggisse de faux articles de faux membres etc. pour effectuer les tests, et à chacune de mes requêtes je vérifie via EXPLAIN et je vérifie le temps d'exécution ceci afin de déterminer ce qui doit être optimisé (ajout d'index, modification des requêtes etc.).

Une des requêtes qui me pose généralement un soucis c'est celle où je dois utiliser des count(*) pour la pagination.

Exemple, voici les tables

TABLE : t_bloc // bloc=article
id_bloc
titre
id_membre_bloc (<-- indique l'id du membre qui a posté l'article, 0 si il s'agit d'un article posté par moi même)
...
(dans mon test il y a 120 000 enregistrements dans cette table)

TABLE : t_taxon_bloc //table qui relie les rubrique et les articles (table de relation)
id_taxon
id_bloc
(dans mon test il y a 120 000 enregistrements dans cette table)

TABLE : t_taxon // dans mon exemple ci dessous je n'aurais pas besoin de faire de jointure avec cette table
id_taxon
taxon (<-- nom de la rubrique)


Voici la requête :

SELECT count(*) AS nb_blocs 
FROM t_bloc B 
INNER JOIN t_taxon_bloc TB 
ON (B.id_bloc=TB.id_bloc) 
WHERE TB.id_taxon=44 
AND B.id_membre_bloc=8

Pour résumer je cherche à compter le nombre d'articles publiés par le membre 8 dans la rubrique 44. reuqête qui me semble pourtant des plus simples et difficilement optimisable :/

Dans mon test le count retourne 60 000 enregistrements sur les 120 000 articles postés (les 60000 autres n'étant pas postés par ce membre)

Mon soucis c'est que la requête met 0,85s pour s’exécuter, autant dire une éternité pour ce type de requête et le peu enregistrement

Voici la gueule de mon explain :

EXPLAIN SELECT count( * ) AS nb_blocs
FROM t_bloc B
INNER JOIN t_taxon_bloc TB ON ( B.id_bloc = TB.id_bloc )
WHERE TB.id_taxon =44
AND B.id_membre_bloc =8

Résultat :

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	B 	ref 	PRIMARY,id_membre_bloc 	id_membre_bloc 	4 	const 	60052 	 
1 	SIMPLE 	TB 	eq_ref 	PRIMARY 	PRIMARY 	8 	const,B.id_bloc 	1 	Using index

id_taxon, id_bloc et id_membre_bloc sont évidemment des index :wink:

Comment éviter éventuellement ces foutus count pour gérer la pagination? Quand il n'y a pas de jointure ca va assez vite mais avec une jointure ça devient très gourmand :/

 

[spout]

Comment éviter éventuellement ces foutus count pour gérer la pagination?

Avec SQL_CALC_FOUND_ROWS: https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#fun ... found-rows

 

[noren]

merci mais en faite utiliser SQL_CALC_FOUND_ROWS ne diminue pas le temps d'exécution.
je me retrouve avec du 0.90s sur ma requête principale.
Donc c'est intéressant dans le fait que ça réduit d'une requête mais pas forcément le temps d'exécution globale de la page.

Ma requête principale faisait 0.05 s en ajoutant le SQL_CALC_FOUND_ROWS elle est donc passé à 0,90s.

J'aimerais donc pouvoir faire un count mais optimisé si aucune autre solution n'existe a part le SQL_CALC_FOUND_ROWS (vu qu'il ne résout pas le temps d'exécution)

Il parait évident que 120 000 enregistrements ce n’est pas dramatique et trouver 60 000 enregistrements parmi ces 120 milles non plus. Rien qui ne justifie les 0,85s de temps d’exécution.

Y a t-il une erreur d'analyse dans ma requête?

Exmeple d'une autre requête que je n'arrive pas à optimiser et qui sont pourtant simple (EXPLAIN m'indique toujours en extra using filesort) :

SELECT B.id_bloc, B.date_modif FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB 
ON (TB.id_bloc=B.id_bloc)
INNER JOIN t_taxon T
ON (T.id_taxon=TB.id_taxon)
WHERE T.parent_id=66 AND B.date_modif>'2000-01-01 00:00:00' 
ORDER BY B.date_modif DESC
limit 10

J'ai 166 000 articles qui répondent aux critères du where. je demande à en récupérer que 10 sachnat que date_modif est un index , idem pour parent_id, id_taxon et id_bloc

résultat il met 2,65s pour me retourner les 10 demandés 8O

et le explain m'indique ceci :

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	T 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	111 	Using where; Using temporary; Using filesort
1 	SIMPLE 	TB 	ref 	PRIMARY 	PRIMARY 	4 	sitajeuxtestbdd1.T.id_taxon 	2150 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY,date_modif_key 	PRIMARY 	4 	sitajeuxtestbdd1.TB.id_bloc 	1 	Using where

On voit bien cette foutu ligne : Using where; Using temporary; Using filesort avec le type =ALL ce qui est très mauvais en terme de perf

Je me tire les cheveux depuis des jours pour optimiser ces foutus requêtes sans succès.

Si j’enlève le order by ça ne met plus que 0,0006s. je ne comprend donc pas pourquoi avec le order by date_modif (champ qui est pourtant en index) la requête explose.

il y a pas a dire, dès que tu commence a faire des tests sur plusieurs milliers d’enregistrements c'est vraiment la merde et il devient très difficile de comprendre comment Mysql interprête nos requêtes qui semblent pourtant des plus simplifiées et optimisées.

petite précision je suis en Myisam

 

[zeb]

count(*) bof bof count(champ) c'est pas plus cool ?

 

[noren]

Non justement, count(*) est optimsé, mysql prend automatiquement la clé primaire ou ce qui est le plus adapté pour compter. :wink:
je pourrais très bien mettre count(B.id_bloc) ça serait équivalent

 

[zeb]

je pourrais très bien mettre count(B.id_bloc) ça serait équivalent

ça me rassure ... J'ai pas testé mais te connaissant je pense qu'il faut tester pour voir (mode stérilisation paranoïaque)

 

[noren]

Oui j'avais déjà essayé, et j'ai essayé à nouveau. C'est une des 1ères choses que j'avais faites :wink:

J'ai vraiment des problèmes pour comprendre le fonctionnement de l'optimiseur :/

Pour cette requête :

SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
INNER JOIN t_taxon_bloc AS TB
ON (TB.id_bloc=B.id_bloc)
INNER JOIN t_taxon T
ON (T.id_taxon=TB.id_taxon)
WHERE T.parent_id=66
ORDER BY B.date_modif DESC
limit 10

0,24s pour me retourner les 10 1er parmi 16000

EXPLAIN :

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	T 	ref 	PRIMARY,parent_id 	parent_id 	4 	const 	12 	Using temporary; Using filesort
1 	SIMPLE 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	T.id_taxon 	31 	Using index
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx_idbloc_date 	PRIMARY 	4 	TB.id_bloc 	1

on voit ces horribles temporary et filesort, l'optimiseur utilise pas ou mal les index et n'effectu surement pas dans le bon ordre la requete :/

J'ai demandé de l'aide également sur un autre forum et on m'a conseillé d'ajouter un index double (date_modif,id_bloc), qui n'a eu aucun effet sur ma requête de départ.
On m'a ensuite demander de tester cette requête :

SELECT B.id_bloc, B.date_modif  
FROM t_bloc AS B
WHERE EXISTS (
 SELECT 1 FROM t_taxon_bloc AS TB 
 INNER JOIN t_taxon T ON T.id_taxon=TB.id_taxon
 WHERE T.parent_id=66 AND TB.id_bloc=B.id_bloc)
ORDER BY B.date_modif DESC
LIMIT 10

EXPLAIN

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	B 	index 	NULL 	idx_date_idbloc 	12 	NULL 	10 	Using where; Using index
2 	DEPENDENT SUBQUERY 	TB 	ref 	PRIMARY,id_bloc 	id_bloc 	4 	B.id_bloc 	1 	 
2 	DEPENDENT SUBQUERY 	T 	eq_ref 	PRIMARY,parent_id 	PRIMARY 	4 	TB.id_taxon 	1 	Using where

Le explian est quasi parfait et la je suis passé à 0.0016s (on voit bien qu'il est possible de fortement réduire en traficotant la requête)
Donc via cette méthode la requête est mieux optimisé mais je n'aime pas trop l'idée des requêtes imbriquées, je craint qu'en production elle me fasse quelques mauvaises surprises.

J'aimerais vraiment pouvoir passer par mes jointures et que la requête soit convenablement optimisée (idem pour les count() )

Si je prend le count() de mon 1er post le probleme ne semble pas vraiment venir du count() lui même mais de la façon dont mysql optimise la requête. je suis persuadé qu'avec les quelques dizaines de milliers d'enregistrements utilisés pour mon test, et la simplicité de la requête, ça devrait être normalement bcp plus rapide pour faire ce count()
Et j'aimerais vraiment pouvoir utiliser de siimple jointure stout en évitant les FORCE INDEX et autres STRAIGHT_JOIN

je pète un plomb avec ces requêtes (que de temps perdus, et le temps me manque)

 

[noren]

Oublions le count() pour le moment

Requête encore plus simple que l'optimiseur mysql n'arrive pas à optimisé et qui met trop de temps (en trouvant le probleme à cette requête, l'histoire du count() et des problèmes d'optimisation de mes jointures devrait être résolu) :

SELECT B.id_bloc
FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB 
ON TB.id_bloc=B.id_bloc
WHERE TB.id_taxon=44 
ORDER BY B.date_modif DESC
LIMIT 0,10

En gros je veux récupérer les 10 1ers articles parmi 292 (dans mon test) classés par date de modification. 44 correspondant à l'id de la rubrique.

Le temps d’exécution est toujours trop long et j'ai toujours ceci :

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	TB 	ref 	PRIMARY,id_bloc 	PRIMARY 	4 	const 	292 	Using index; Using temporary; Using filesort
1 	SIMPLE 	B 	eq_ref 	PRIMARY,idx_idbloc_date 	PRIMARY 	4 	TB.id_bloc 	1

par contre si j’enlève le order by tout est ok.

Donc le ORDER BY B.date_modif DESC n’est pas bien géré.

J'ai pourtant essayé l'index sur date_modif ou les index combinés (date_modif, id_bloc) ou (id_bloc,date_modif)

 

[Blount]

Essaie de remplacer "INNER JOIN" par "LEFT JOIN".
Dans un de mes tests, le INNER JOIN utilise une table temporaire (Using temporary) alors que LEFT JOIN non.

 

[noren]

Essai effectué et même problème :/

Toujours les using temporary et filesort et temps d'execution trop long.

Grr je suis sur 3 gros forums a poser la question et je trouve toujours pas la cause de problème :cry:

 

[Blount]

Tu ne peux pas nous générer un jeu de donner pour effectuer les tests dans tes conditions ?

 

[noren]

Comment je fais ca? :mrgreen:

t_bloc
id_bloc titre date_modif
1 titre1 2014-01-02 00:00:00
2 titre2 2014-01-03 00:00:00
3 titre3 2014-01-04 00:00:00
4 titre10 2014-01-11 00:00:00
5 titre4 2014-01-05 00:00:00
6 titre5 2014-01-06 00:00:00
7 titre7 2014-01-08 00:00:00
8 titre6 2014-01-07 00:00:00
9 titre9 2014-01-10 00:00:00
10 titre8 2014-01-09 00:00:00

t_taxon
id_taxon titre
1 rubrique1
2 rubrique2
3 rubrique3
4 rubrique4

t_taxon_bloc
id_taxon id_bloc
1 4
1 5
1 8
2 3
2 5
2 10
3 1
3 2
3 4
3 9
4 7

je pense qu'il est inutile d'en donner des centaines si il y a un soucis avec la requête ça se verra meme avec une poignée d'enregistrements :wink:
En tout cas chez moi que j'ai quelques centaines ou milliers d'enregistrements, le problème est le même


PS sur un autre forum on m'a proposer également de forcer l'index (date_modif, id_bloc)

SELECT B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc) 
INNER JOIN t_taxon_bloc AS TB 
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=44 
order by B.date_modif DESC
limit 0,10

idx_date_idbloc = INDEX (date_modif, id_bloc)

cette solution est 3 a 4 fois plus rapide, je n'ai plus de use TEMPORARY et FILESORT, mais forcer un index.. :/

Que ca soit avec une requete imbriquée via EXISTS ou FORCE INDEX je n'arrive pas à être persuadé du bien fondé de ces solutions :/

Comme je l'ai dit je trouve a la base ma requête et mon probleme suffisamment simple même pour MYSQL, à la base :/
Et elle me donne vraiment pas l'impression d'avoir besoin de subterfuges

 

[zeb]

Comment je fais ca ?

Tu exporte les tables concernées (structure et donnée) en sql par exemple et tu nous met un lien de DL dans un coin pour pouvoir récupérer le fichier car monter les tables et peupler a la main c'est disons un peut ... chiant.

 

[noren]

oui c'est vrai c'est pas faut :wink:

je peux éventuellement fournir le code sql ici y aura plus qu'a le copier dans phpmyadmin :wink:

J'essaye de faire ca demain, la je vais manquer un peu de temps

 

[noren]

Voilou un jeu de données comme demandé.
je l'ai testé et j'ai le même problème avec ce jeu de données :wink:

ici je crée que les tables et insert les données, je ne crée pas la base :wink:
je n'ai évidemment pas mis tout les champs superflux de ma vrai bdd, mais ce n'est pas nécessaire puisque j'ai le même soucis : USINg temporary et using filesort qui rallonge considérablement le temps d’exécution des que j'ai pas mal d'articles (blocs).

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mer 19 Février 2014 à 10:56
-- Version du serveur: 5.1.30
-- Version de PHP: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de données: `testbdd`
--

-- --------------------------------------------------------

--
-- Structure de la table `t_bloc`
--

CREATE TABLE IF NOT EXISTS `t_bloc` (
  `id_bloc` int(10) NOT NULL AUTO_INCREMENT,
  `titre` varchar(100) NOT NULL,
  `date_modif` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id_bloc`),
  KEY `idx_date_idbloc` (`date_modif`,`id_bloc`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Contenu de la table `t_bloc`
--

INSERT INTO `t_bloc` (`id_bloc`, `titre`, `date_modif`) VALUES
(1, 'titre1', '2014-01-01 00:00:01'),
(2, 'titre2', '2014-01-01 00:00:02'),
(3, 'titre3', '2014-01-01 00:00:03'),
(4, 'titre4', '2014-01-01 00:00:04'),
(5, 'titretest1', '2014-02-19 12:21:50'),
(6, 'titretest1', '2014-02-19 12:21:59'),
(7, 'titretest1', '2014-02-19 12:22:00'),
(8, 'titretest1', '2014-02-19 12:22:01'),
(9, 'titretest1', '2014-02-19 12:22:02'),
(10, 'titretest1', '2014-02-19 12:22:03'),
(11, 'titretest1', '2014-02-19 12:22:05'),
(12, 'titretest1', '2014-02-19 12:22:06'),
(13, 'titretest1', '2014-02-19 12:22:07'),
(14, 'titretest1', '2014-02-19 12:22:08'),
(15, 'titretest1', '2014-02-19 12:22:09'),
(16, 'titretest1', '2014-02-19 12:22:10'),
(17, 'titretest1', '2014-02-19 12:22:12'),
(18, 'titretest1', '2014-02-19 12:22:13'),
(19, 'titretest1', '2014-02-19 12:22:14'),
(20, 'titretest1', '2014-02-19 12:22:15'),
(21, 'titretest1', '2014-02-19 12:22:16'),
(22, 'titretest1', '2014-02-19 12:22:17'),
(23, 'titretest1', '2014-02-19 12:23:28');

-- --------------------------------------------------------

--
-- Structure de la table `t_taxon`
--

CREATE TABLE IF NOT EXISTS `t_taxon` (
  `id_taxon` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `titre` varchar(100) NOT NULL,
  PRIMARY KEY (`id_taxon`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Contenu de la table `t_taxon`
--

INSERT INTO `t_taxon` (`id_taxon`, `titre`) VALUES
(1, 'rubrique1'),
(2, 'rubrique2'),
(3, 'rubrique3'),
(4, 'rubrique4');

-- --------------------------------------------------------

--
-- Structure de la table `t_taxon_bloc`
--

CREATE TABLE IF NOT EXISTS `t_taxon_bloc` (
  `id_taxon` int(10) unsigned NOT NULL,
  `id_bloc` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_taxon`,`id_bloc`),
  KEY `id_bloc` (`id_bloc`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Contenu de la table `t_taxon_bloc`
--

INSERT INTO `t_taxon_bloc` (`id_taxon`, `id_bloc`) VALUES
(1, 2),
(1, 3),
(1, 6),
(1, 18),
(1, 19),
(1, 20),
(2, 1),
(2, 4),
(2, 5),
(2, 12),
(2, 14),
(2, 17),
(2, 23),
(3, 1),
(3, 3),
(3, 7),
(3, 11),
(3, 13),
(3, 16),
(3, 22),
(4, 2),
(4, 8),
(4, 9),
(4, 10),
(4, 15),
(4, 21);

je vous remercies en tout cas pour l'aide que vous m'apportez

 

[Blount]

Sur mon MySQL (5.5) local, il m'affiche 0.00s de temps d'exécution des différentes requêtes.

Ton serveur SQL est bien configuré ? Tu utilises un serveur SQL en prod ou une version de dev non chargé ?

 

[noren]

la il ne faut évidemment pas regardé le temps d'execution étant donné le peu d'enregistrements. (Il manquerait plus qu'il soit lent avec 20 pauvres articles ^^ )
Il faut regarder le explain :wink:

Si je met 10 000 enregistrements ce qui est peu le temps d'exeution devient déjà beaucoup plus lent. je suis obligé de faire un FORCE INDEX sur idx-date_idbloc ou d'utiliser des requêtes imbriquées via EXISTS pour diviser par 10 le temps d’exécution et utiliser convenablement les indexes :wink:

En gros il faut que j'arrive a virer ces fichus temporary et filesort

je fais des tests sur mon pc perso (WAMPSERVER 2.0), certes pas forcément le plus adapté. Mais je ne suis pas persuadé que ça soit la source du probleme.

je pense que c'est lié à la requête qui empêche une utilisation convenable des index :?

as tu testé avec cette requête :

SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
INNER JOIN t_taxon_bloc AS TB 
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=2 
order by B.date_modif DESC
limit 0,10

puis avec cette requête avec le force index

SELECT B.id_bloc, B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc) 
INNER JOIN t_taxon_bloc AS TB 
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=2 
order by B.date_modif DESC
limit 0,10

en regardant pour chacune le EXPLAIN (tu verras qu'en forçant l'index (ce que j'aime pas) la requête est pourtant bien mieux optimisé et ca se sens au niveau temps d’exécution quand j'ai plus de 10 000 ou 100 000 enregistrements :wink:

 

[Blount]

Là, je pense avoir bien rempli les tables:

mysql> SELECT COUNT(*) FROM t_bloc;
+----------+
| COUNT(*) |
+----------+
|   327680 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(*) FROM t_taxon;
+----------+
| COUNT(*) |
+----------+
|    32768 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t_taxon_bloc;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

mysql>     SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
    ->     INNER JOIN t_taxon_bloc AS TB
    ->     ON (TB.id_bloc=B.id_bloc)
    ->     WHERE TB.id_taxon=2
    ->     order by B.date_modif DESC
    ->     limit 0,10;
+---------+---------------------+
| id_bloc | date_modif          |
+---------+---------------------+
|      23 | 2014-02-19 12:23:28 |
|      22 | 2014-02-19 12:22:17 |
|      21 | 2014-02-19 12:22:16 |
|      20 | 2014-02-19 12:22:15 |
|      19 | 2014-02-19 12:22:14 |
|      18 | 2014-02-19 12:22:13 |
|      40 | 2014-02-19 12:22:12 |
|      17 | 2014-02-19 12:22:12 |
|      39 | 2014-02-19 12:22:10 |
|      16 | 2014-02-19 12:22:10 |
+---------+---------------------+
10 rows in set (0.04 sec)

mysql>     SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
    ->     INNER JOIN t_taxon_bloc AS TB
    ->     ON (TB.id_bloc=B.id_bloc)
    ->     WHERE TB.id_taxon=2
    ->     order by B.date_modif DESC
    ->     limit 0,10;
+---------+---------------------+
| id_bloc | date_modif          |
+---------+---------------------+
|      23 | 2014-02-19 12:23:28 |
|      22 | 2014-02-19 12:22:17 |
|      21 | 2014-02-19 12:22:16 |
|      20 | 2014-02-19 12:22:15 |
|      19 | 2014-02-19 12:22:14 |
|      18 | 2014-02-19 12:22:13 |
|      40 | 2014-02-19 12:22:12 |
|      17 | 2014-02-19 12:22:12 |
|      39 | 2014-02-19 12:22:10 |
|      16 | 2014-02-19 12:22:10 |
+---------+---------------------+
10 rows in set (0.04 sec)

Tu utilises des table MyISAM, tu as essayé avec innoDb ?
Ma version 5.5 en est aussi peut-être pour quelque chose.

 

[noren]

Arf il y a un truc qui m'échappe.
Après ca dépend peut être des enregistrements et de leur répartition.
Normalement la configuration des tables voudrait qu'il y ai au pires quelques dizaines de d'enregistrements dans t_taxons, et 1à5 fois plus d'enregistrements dans t_taxon_bloc que dans t_bloc

donc si tu as 365 000 dans t_blocs je devrais avoir au moins 365 000 enregistrements dans t_taxon_bloc voir jusqu'à 5 fois plus (en moyenne)
puisqu'un article (bloc) peut etrte associé à un ou plusieurs taxons (rubrique, tags...)

Peux tu juste me transmettre le résultat de ceci :

EXPLAIN SELECT B.id_bloc, B.date_modif FROM t_bloc AS B
INNER JOIN t_taxon_bloc AS TB
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=2
order by B.date_modif DESC
limit 0,10

et le résultat de ceci :

EXPLAIN SELECT B.id_bloc, B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc) 
INNER JOIN t_taxon_bloc AS TB
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=2
order by B.date_modif DESC
limit 0,10

Je voudrais voir ce que retourne ton EXPLAIN

Après si eventuellement tu peux m'en envoyer le fichier sql de ta batterie de tests pour que j'essaye chez moi et voir si je retrouve exactement le même temps d'execution :wink: , je te transmet mon email en MP? (quoi que ca va être un peu lourd par mail)

oui j'utilise le moteur Myisam, j'avais également essayé de transformer ces 3 tables en innodb, de lancer pour chacune d'elle optimize table
mais j'obtenais les mêmes résultats.
possible que ça vienne en effet de mon serveur mysql (sur mon pc j'utilise la version 5.1.30, mais aucune hypothèse ne doit ne doit être mise de côté

En tout cas sympas de ta part d'effectuer ces tests :wink:

 

[noren]

Je viens de voir que tu as fait le test 2 fois avec la même requete

tu n'as pas essayé avec celle ci :

SELECT B.id_bloc, B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc)
INNER JOIN t_taxon_bloc AS TB
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=2
order by B.date_modif DESC
limit 0,10

tu devrais normalement avoir un temps d’exécution bien inférieur a 0.04s :wink:

 

[noren]

je viens de remarquer un autre truc étrange et qui me surprend pas non plus étant donné que je craignais le côté instable du FORCE INDEX

En fonction du nombre d'articles dans une rubrique le force index peut devenir nuisible.

Exemple avec une rubrique contenant 5000 enregistrements avec le force index était plus efficace
mais des que je lance la requête sur une rubrique avec 1200 enregistrements paff (le chien) force index est bcp moins efficace que sans!

je devient fou avec ces foutus requêtes (pinaise d'optimiseur mysql!!!)

 

[noren]

En continuant de faire des recherches je suis tombé sur ceci (et d'autres résultats qui vont toujours dans le même sens) :

http://stackoverflow.com/questions/5758352/mysql-query-file-sort-when- ... d-order-by

une phrase pertinente s'y trouve et c'est ce que je pensais :

I don't see how this can be optimized more, because in the where/order clause you have values from two different tables and you can't create a composite index (fk_Authors,publicationDate)

C'est exactement mon cas ici, il semblerait donc que je n'ai que 3-4 choix possibles qui ont tous leurs avantages et leurs défauts :

- Utiliser STRAIGHT_JOIN
- utiliser FORCE INDEX
- utililiser des requêtes imbriquées soit via EXISTS soit via IN
ou
- dénormaliser ma BDD

je crois que je vais donc combiner un peu tout ca en fonction de la requête qui fera appel aux taxons.
Pour dénormaliser et éviter des jointures qui peuvent devenir lourde je vais donc dupliquer l'id_taxon correspondant aux rubriques que je vais ajouter dans la table t_bloc.
Étant donné qu'un article ne peut être associé qu'à une seule rubrique cette dénormalisation peut être effectué.
inconvénient duplication de cette info :/

et pour les requêtes qui font appel aux tags (par exemple) j'essayerais de voir parmis les 3 autres choix lequel est le plus efficace

mais je dois avouer quand même que je pensais pas autant galérer la dessus et que je ne pensais pas rencontrer de soucis d'optimisation avec cette construction de table qui est en fin de compte très proche de celle de wordpress (mais bon j'ai pas le même niveau techniquement)

Autre petite question :

Quand vous avez des rubriques sur vos sites qui peuvent contenir plusieurs dizaines voir centaines de pages. Admettons une section blog. Comment faites vous pour gérer cette pagination?
Cela implique généralement de compter à chaque fois le nombre d'articles (ce qui peut être lourd, peu importe la méthode utiliser count() ou SQL_CALC_FOUND_ROWS). Dans mon cas il est impossible de stocker dans la bdd le nombre d'articles par rubrique pour des raisons trop longues à expliquer
De plus un LIMIT 1000,0 peut également devenir gourmand par rapport au LIMIT 0,10

 

[YoyoS]

En général on s'en fou de gagner 0.Xs quand tu mets en cache tes pages pendant Xmin ou heures et que pour améliorer ton temps t'es obligé de te tirer les cheveux

Les requêtes count sont dans l'absolu les plus rapides qui soient et la plupart du temps on les utilise sans jointures, résultat instantanné si t'as mis un cache assez conséquent des indexs.

Les grosses requêtes ne sont pas sensées être exécutées en permanence sur des pages très fréquentées sinon il y a un problème de conception et de logique à la base.

 

[noren]

Oui j'avais également prévu de mettre mes pages en cache X minutes, mais c'est quand même frustrant de pas arriver à un résultat correct même avec quelques dizaines de milliers d'enregistrements. :?

malheureusement dans mes count je serais obligé de faire des jointures, je n'ai pas le choix :wink:

De plus les pages qui seront les plus consultées seront les pages les plus gourmandes et ce n’est pas forcément un probleme lié a la conception du départ. en tout cas je voix difficilement comment faire mieux.

On est bien d'accord que si un article peut être associé a plusieurs tags on est obligé d'avoir une table de relation?

t_articles
id_article
article
en_ligne
date_modif

t_tags
id_tag
tag

t_tag_article
id_tag
id_article

c'est exactement le schéma de ma BDD

Donc si je veux afficher les articles ayant pour tag 1

je suis bien obligé de faire :

SELECT A.id_article, A.date_modif FROM t_articles AS A 
INNER JOIN t_tag_article AS TA 
ON (TA.id_article=A.id_article) 
AND TA.id_tag=1
order by A.date_modif, A.en_ligne=1
limit 10

Et si je veux compter tous les articles qui ont pour tag 1 et les afficher par date_modif je suis bien obligé d'avoir une jointure?

dites moi si je fais fausse route car si ma requête est juste, il semble impossible de l'optimiser avec les jointures à cause TA.id_tag=1 qui empêche l'utilisation de l'index sur le champ date_modif et qui rend la requête trop lourde

J'ai cherché dans le code de wordpress et je n'ai pas trouvé comment ils affichent la liste de leurs articles car la requête devrait m'aiguiller pour la mienne étant la construction très proche de la gestion des taxons (tags, rubriques)

Comment gérer une relation 0-n autrement?

 

[zeb]

Donc si je veux afficher les articles ayant pour tag 1
je suis bien obligé de faire :

bah non.

SELECT
A.id_article, A.date_modif
FROM
t_articles AS A,
t_tag_article AS TA
WHERE
TA.id_article=A.id_article AND
TA.id_tag=1
ORDER BY ...

Je peux pas dire la différence avec ou sans jointure mais disons que tu n'est pas obligé de la faire.

Et si je veux compter tous les articles qui ont pour tag 1 et les afficher par date_modif je suis bien obligé d'avoir une jointure?

Il y a (pour moi) un souci dans l'énoncé car le comptage implique la prise en compte du tout et par principe ne change pas souvent (donc une data peux évolutive)
Et de l'autre côté, afficher une liste par date ne se fait pas souvent au complet ... bref c'est pas compatible avec le count ...

Et dans tous les cas je ne vois pas l'intérêt de la jointure sauf a comparer les temps d’exécution et qu'une requête basique serait moins performante.

 

[noren]

bah non.

SELECT
A.id_article, A.date_modif
FROM
t_articles AS A,
t_tag_article AS TA
WHERE
TA.id_article=A.id_article AND
TA.id_tag=1
ORDER BY ...

cette requête est une jointure ( en un poil moin propre ) cela ne change rien au niveau des performances et de l'utilisation des indexes, j'ai tourné viré dans tous les sens les requêtes et j'en arrive au même point.

Je peux pas dire la différence avec ou sans jointure mais disons que tu n'est pas obligé de la faire.

En fait si, ce que tu as proposé est une jointure, sinon je dois passer par une requete imbriquée ou un forcage d'index réellement pas propre.

la jointure est obligatoire dans la mesure ou tu es obligé de faire la relation entre t_tag_article et t_articles

sans macondition A.en_ligne=1 il est clair que j'avais juste à compter dans t_tag_article le nombre d'enregistrement avec id_tag=1 et le tour était joué :wink:
Et la c'est clair que le count() est quasi instantané.

Il y a (pour moi) un souci dans l'énoncé car le comptage implique la prise en compte du tout et par principe ne change pas souvent (donc une data peux évolutive)
Et de l'autre côté, afficher une liste par date ne se fait pas souvent au complet ... bref c'est pas compatible avec le count ...

Pour le date_modif je parle dans le select indiqué un peu plus haut (qui est fausse, j'ai des erreurs de syntaxes, je la rectifi un peu plus bas) pas dans le count :wink:
désolé si j'ai été confus dans mon explication

SELECT A.id_article, A.date_modif 
FROM t_articles AS A
INNER JOIN t_tag_article AS TA
ON (TA.id_article=A.id_article)
WHERE TA.id_tag=1 AND A.en_ligne=1
order by A.date_modif DESC
limit 10

Donc hormis la requête imbriquée avec EXISTS ou de forcer l'index, je ne vois pas d'autres moyens d'optimisation de ces requetes. Via les jointures il ne semble en tout cas pas y avoir de solution.

Et dans tous les cas je ne vois pas l'intérêt de la jointure sauf a comparer les temps d’exécution et qu'une requête basique serait moins performante.

il n'y a pas de requêtes basiques ou de requêtes avec jointures. Dès le moment que tu lis de stables entre elles dans une requête tu as une jointure que tu l'écris sous la forme JOIN ou avec une simple ",". "," est traduit par JOIN par l'optimiseur.

Le problème semble a priori venir de la :

I don't see how this can be optimized more, because in the where/order clause you have values from two different tables and you can't create a composite index (fk_Authors,publicationDate)

je suis tombé sur pas mal de sites ou l'on posait la même question, et on obtient toujours les mêmes réponses

alors je pourrais essayer de trouver un moyen de ne pas avoir ce : en_ligne=1 dans la clause where de mon count() ca réduirait considérablement le temps d’exécution des count. Mais je ne règlerais pas le soucis des SELECT à cause du date_modif.

Pour le count je penseopter en fin de compte pour l'ajout d'un champ nb_blocs dans ma table t_taxon.
A chaque fois que j'ajoute, met à jour, supprime ou déplace de rubrique un article, je fais un update de tous les nb_blocs des différents taxons qui lui sont associés.
de cette faon même si cette très grosse requête met 0.5s ou 2s elle est rarement exécuté.
En en ligne tous les count seront quasi instantané

ensuite pour les select je choisirais en fonction des cas la meilleur requete (jointure ou requêtes imbriquées)

je crois que je n'ai plus vraiment le choix et je dois maintennat avancer, je suis bloqué depuis trop longtemps sur ces fichus requetes.

PS : il semblerait que postgre ou sqllite optimise beaucoup mieux ces requêtes, mais mysql a beaucoup de mal (malheureusement je suis sur mysql)