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

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par noren, 15 Février 2014.

  1. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    Code:
    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 :

    Code:
    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 :
    Code:
    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 :/
     
  2. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 115
    J'aime reçus:
    315
  3. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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) :

    Code:
    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 :

    Code:
    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
     
  4. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 190
    J'aime reçus:
    1
    count(*) bof bof count(champ) c'est pas plus cool ?
     
  5. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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
     
  6. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 190
    J'aime reçus:
    1
    ça me rassure ... J'ai pas testé mais te connaissant je pense qu'il faut tester pour voir :D (mode stérilisation paranoïaque)
     
  7. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    Code:
    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 :

    Code:
    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 :

    Code:
    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

    Code:
    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)
     
  8. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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) :

    Code:
    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 :
    Code:
    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)
     
  9. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    701
    J'aime reçus:
    0
    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.
     
  10. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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:
     
  11. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    701
    J'aime reçus:
    0
    Tu ne peux pas nous générer un jeu de donner pour effectuer les tests dans tes conditions ?
     
  12. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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)

    Code:
    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
     
  13. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 190
    J'aime reçus:
    1
    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. :D
     
  14. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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
     
  15. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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).


    Code:
    -- 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
     
  16. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    701
    J'aime reçus:
    0
    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é ?
     
  17. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    Code:
    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

    Code:
    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:
     
  18. Blount
    Blount WRInaute impliqué
    Inscrit:
    18 Novembre 2010
    Messages:
    701
    J'aime reçus:
    0
    Là, je pense avoir bien rempli les tables:
    Code:
    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)
    
    Code:
    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)
    
    Code:
    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.
     
  19. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    Code:
    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 :

    Code:
    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:
     
  20. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    Code:
    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:
     
  21. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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!!!)
     
  22. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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 :

    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
     
  23. YoyoS
    YoyoS WRInaute accro
    Inscrit:
    14 Septembre 2006
    Messages:
    3 249
    J'aime reçus:
    0
    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 :p

    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.
     
  24. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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?

    Code:
    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 :

    Code:
    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?
     
  25. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 190
    J'aime reçus:
    1
    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.

    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.
     
  26. noren
    noren WRInaute accro
    Inscrit:
    8 Avril 2011
    Messages:
    2 906
    J'aime reçus:
    14
    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.

    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é.

    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

    Code:
    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.

    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 :

    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)
     
Chargement...
Similar Threads - Problème optimisation count() Forum Date
Test d'optimisation mobile : Problèmes de chargement de la page Débuter en référencement 10 Novembre 2019
probleme optimisation? Débuter en référencement 27 Avril 2012
Problème d'optimisation de la base de donnée? ou problème du serveur Développement d'un site Web ou d'une appli mobile 11 Avril 2010
Problème d'optimisation de rand () Développement d'un site Web ou d'une appli mobile 24 Février 2010
URL canonique et problème entre / et /index.php Débuter en référencement Hier à 18:53
Problème avec Yoast SEO Débuter en référencement 13 Janvier 2021
Problèmes de trafic incorrect Annonces Limités AdSense 2 Janvier 2021
problème indexation Crawl et indexation Google, sitemaps 25 Décembre 2020
Problème d’indexation de nos articles récents suit à un piratage Crawl et indexation Google, sitemaps 15 Décembre 2020
Problème d'indexation des fiches produits et page parente Crawl et indexation Google, sitemaps 1 Décembre 2020