Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

Index suite à log-queries-not-using-indexes


Sujet :

Requêtes MySQL

  1. #1
    Membre actif
    Inscrit en
    septembre 2004
    Messages
    433
    Détails du profil
    Informations forums :
    Inscription : septembre 2004
    Messages : 433
    Points : 257
    Points
    257
    Par défaut Index suite à log-queries-not-using-indexes
    Bonjour,

    J'ai de gros soucis de lenteur sur ma bdd, du coup je recherche comment optimisé mes tables, j'ai activé le log-queries-not-using-indexes
    Dans la log, j'ai ce type de requête qui est ressortie, quel type d'index placeriez vous ??

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    # Query_time: 1  Lock_time: 0  Rows_sent: 1005  Rows_examined: 548139
    SELECT t_game2.ID AS ID, IDaraj,youth, IDclub, IDclub2, adate, atime,daraj_test, araj_test,cup,COALESCE(t_araj_test.sort,50000) AS gmSort,status,cl1.club AS club1, cl1.dclub AS dclub1, cl2.dclub AS dclub2,cl2.club AS club2, t_araj_test.IDcountry AS IDcountry, country,t_country.sms AS countryCode, result, result2, resultH, resultH2, resultF, resultF2, resultE, resultE2, resultP, resultP2, isLast 
    FROM t_game2 LEFT JOIN t_araj_test ON t_araj_test.ID=IDaraj 
    LEFT JOIN t_country ON t_country.ID=t_araj_test.IDcountry 
    LEFT JOIN t_club as cl1 ON cl1.ID=IDclub  
    LEFT JOIN t_club as cl2 ON cl2.ID=IDclub2 
    WHERE ( (adate > '2019-10-05 11:00:00' and adate < '2019-10-06 15:00:00') ) AND cl1.display=1 AND cl2.display=1  ORDER BY COALESCE(t_araj_test.sort,50000), araj_test, adate, atime;
    Merci beaucoup pour votre aide

  2. #2
    Expert éminent sénior

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    4 910
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 4 910
    Points : 13 846
    Points
    13 846
    Billets dans le blog
    1
    Par défaut
    Bonjour,

    On ne peut pas vous aider sur un problème de performances sans la description exacte des tables (de préférence, communiquez le DDL complet des ordres CREATE TABLE et CREATE INDEX)

    Ce qu'il faut vérifier en premier, c'est la présence d'index sur des colonnes de même type et même longueur pour les critères de filtrage et de jointure

    Quelques remarques en passant :
    • vos jointures OUTER sont implicitement remplacées par des jointures INNER à cause des restrictions AND cl1.display=1 AND cl2.display=1
    • supprimez les parenthèses dans la clause WHERE, il n'y a que des AND, elles sont donc in
    • vous devriez utiliser des alias pour chaque table et préfixer toutes les colonnes par ces alias, la compréhension de la requête en sera facilitée (ici on ne sait pas quelle colonne vient de quelle table)

  3. #3
    Membre régulier
    Homme Profil pro
    Analyste-programmeur
    Inscrit en
    décembre 2014
    Messages
    45
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France

    Informations professionnelles :
    Activité : Analyste-programmeur
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2014
    Messages : 45
    Points : 87
    Points
    87
    Par défaut
    Citation Envoyé par omelhor Voir le message
    Bonjour,

    J'ai de gros soucis de lenteur sur ma bdd, du coup je recherche comment optimisé mes tables, j'ai activé le log-queries-not-using-indexes
    Dans la log, j'ai ce type de requête qui est ressortie, quel type d'index placeriez vous ??
    Que dit le EXPLAIN de cette requête? Et de quoi ont l'air les tables impliquées?

  4. #4
    Membre actif
    Inscrit en
    septembre 2004
    Messages
    433
    Détails du profil
    Informations forums :
    Inscription : septembre 2004
    Messages : 433
    Points : 257
    Points
    257
    Par défaut
    Bonjour,

    Je m'excuse pour la réponse tardive, voici la structure de tables, et le résultat du EXPLAIN

    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
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    -- --------------------------------------------------------
    
    --
    -- Structure de la table `t_game2`
    --
    
    CREATE TABLE IF NOT EXISTS `t_game2` (
      `ID` int(7) NOT NULL,
      `IDclub` int(8) NOT NULL default '0',
      `IDclub2` int(8) NOT NULL default '0',
      `adate` date NOT NULL default '0000-00-00',
      `IDreferee` smallint(2) default NULL,
      `IDreferee2` smallint(2) unsigned NOT NULL default '0',
      `IsLast` smallint(1) default NULL,
      `mainTime` int(11) NOT NULL default '0',
      `addedTime` float(10,2) NOT NULL default '0.00',
      `game` varchar(10) NOT NULL default '',
      `IDaraj` int(11) NOT NULL default '0',
      `IDstadium` varchar(50) default NULL,
      `IDlevel` tinyint(3) unsigned NOT NULL default '0',
      `IDgrup` tinyint(3) unsigned NOT NULL default '0',
      `atime` time default NULL,
      `resultH` tinyint(3) unsigned NOT NULL default '0',
      `resultH2` tinyint(3) unsigned NOT NULL default '0',
      `result` tinyint(3) unsigned NOT NULL default '0',
      `result2` tinyint(3) unsigned NOT NULL default '0',
      `resultF` tinyint(3) unsigned NOT NULL default '0',
      `resultF2` tinyint(3) unsigned NOT NULL default '0',
      `resultE` tinyint(3) unsigned NOT NULL default '0',
      `resultE2` tinyint(3) unsigned NOT NULL default '0',
      `resultP` tinyint(3) unsigned NOT NULL default '0',
      `resultP2` tinyint(3) unsigned NOT NULL default '0',
      `status` tinyint(3) unsigned NOT NULL default '0',
      `home` float(10,2) default NULL,
      `draw` float(10,2) default NULL,
      `away` float(10,2) default NULL,
      `homeLink` text,
      `coach1` varchar(100) default NULL,
      `coach2` varchar(100) default NULL,
      `statistic` tinyint(4) NOT NULL default '0',
      `home_ch` float(10,3) default NULL,
      `draw_ch` float(10,3) default NULL,
      `away_ch` float(10,3) default NULL,
      `homeLink_ch` text,
      `referee` varchar(100) default NULL,
      `stadium` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
      `penal` tinyint(3) unsigned default '0',
      `att` int(11) NOT NULL default '0',
      `frms` varchar(10) NOT NULL default '',
      `frms2` varchar(10) NOT NULL default '',
      `season` varchar(15) default NULL,
      `agrRes` varchar(50) NOT NULL default '',
      `agrWin` tinyint(4) NOT NULL default '0',
      `startTm` int(11) NOT NULL default '0',
      `day` smallint(3) default NULL,
      `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `weather1` varchar(100) NOT NULL default '',
      `weather2` varchar(100) NOT NULL default '0',
      `weather3` int(11) NOT NULL default '0',
      `disabled` tinyint(3) unsigned NOT NULL default '0',
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `ccda` (`IDclub`,`IDclub2`,`adate`,`IDaraj`),
      KEY `IDclub` (`IDclub`),
      KEY `IDclub2` (`IDclub2`),
      KEY `idx_adate` (`adate`),
      KEY `idx_IDaraj` (`IDaraj`),
      KEY `idx_IDclub2_adate` (`IDclub2`,`adate`),
      KEY `idx_IDclub_adate` (`adate`,`IDclub`),
      KEY `idx_status` (`status`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    
    -- --------------------------------------------------------
    
    --
    -- Structure de la table `t_araj_test`
    --
    
    CREATE TABLE IF NOT EXISTS `t_araj_test` (
      `ID` int(11) NOT NULL,
      `araj_test` varchar(100) NOT NULL default '',
      `araj_test_country` varchar(100) NOT NULL,
      `daraj_test` varchar(100) default NULL,
      `daraj_test_old` varchar(100) NOT NULL,
      `id2` smallint(6) NOT NULL,
      `stand_actif` int(11) NOT NULL default '0',
      `display` tinyint(2) NOT NULL,
      `cup` tinyint(3) NOT NULL default '0',
      `champ` int(11) NOT NULL,
      `sort` smallint(6) NOT NULL default '0',
      `date_last_match` date NOT NULL,
      `feed` tinyint(2) NOT NULL,
      `sort_fr` smallint(6) NOT NULL default '0',
      `IDcountry` int(11) NOT NULL default '0',
      `first` tinyint(4) NOT NULL default '0',
      `youth` tinyint(4) NOT NULL default '0',
      `IDsport` tinyint(4) NOT NULL default '1',
      `araj_test_ch` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
      `araj_test_ch_country` varchar(100) default NULL,
      `araj_seo` varchar(100) NOT NULL,
      `araj_seo_old` varchar(100) NOT NULL,
      `araj_seo_comp` varchar(100) NOT NULL,
      `url` varchar(255) NOT NULL,
      `youtube` varchar(255) NOT NULL,
      `twitter` varchar(255) NOT NULL,
      `facebook` varchar(255) NOT NULL,
      `google` varchar(255) NOT NULL,
      `instagram` varchar(255) NOT NULL,
      `wap` varchar(50) NOT NULL default '',
      `showCountry` tinyint(2) NOT NULL default '0',
      `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `IDstage` int(11) unsigned default NULL,
      `disabled` tinyint(3) NOT NULL default '0',
      `priority` tinyint(3) NOT NULL default '0',
      `teams` tinyint(3) NOT NULL default '0',
      `gmBetween` tinyint(3) NOT NULL default '0',
      `IDcompetition` int(11) NOT NULL default '0',
      `commentaire` text NOT NULL,
      `hasGames` tinyint(1) NOT NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `araj_test` (`araj_test`),
      UNIQUE KEY `idx_daraj_test` (`daraj_test`),
      KEY `idx_sort` (`sort`),
      KEY `idx_IDcountry` (`IDcountry`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    -- --------------------------------------------------------
    
    --
    -- Structure de la table `t_club`
    --
    
    CREATE TABLE IF NOT EXISTS `t_club` (
      `ID` int(11) NOT NULL,
      `club` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL default '',
      `IDcountry` int(11) NOT NULL default '0',
      `coach` varchar(50) default NULL,
      `dclub` varchar(50) default NULL,
      `hasGames` tinyint(1) NOT NULL,
      `display` tinyint(4) NOT NULL default '0',
      `IDbdfutbol` int(11) NOT NULL,
      `IDxmlsoccer` int(11) NOT NULL,
      `dateMaj` date default NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `idx_dclub` (`dclub`),
      KEY `idx_display` (`display`),
      KEY `idx_club` (`club`),
      KEY `idx_ID_display` (`ID`,`display`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    
    -- --------------------------------------------------------
    
    --
    -- Structure de la table `t_country`
    --
    
    CREATE TABLE IF NOT EXISTS `t_country` (
      `ID` int(11) NOT NULL,
      `IDcontinent` tinyint(3) NOT NULL default '0',
      `country` varchar(50) NOT NULL default '',
      `Demonym` varchar(100) NOT NULL,
      `wiki_en` varchar(255) NOT NULL,
      `display` int(1) NOT NULL,
      `country_seo` varchar(100) NOT NULL,
      `country_ru` varchar(50) NOT NULL default '',
      `country_ch` varchar(50) NOT NULL default '',
      `country_tr` varchar(50) default NULL,
      `country_hb` varchar(50) default NULL,
      `country_nl` varchar(50) default NULL,
      `sms` char(2) default NULL,
      `country_de` varchar(50) default NULL,
      `country_fr` varchar(100) NOT NULL default '',
      `country_fr_seo` varchar(100) NOT NULL,
      `habitant_fr` varchar(100) NOT NULL,
      `wiki_fr` varchar(255) NOT NULL,
      `country_am` varchar(250) NOT NULL default '',
      `country_r2` varchar(200) NOT NULL default '',
      `bgcolor` varchar(10) NOT NULL default '',
      `fgcolor` varchar(10) NOT NULL default '',
      `flag` varchar(50) NOT NULL default '',
      `flag_fr` varchar(50) NOT NULL,
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `country_idx` (`country`),
      UNIQUE KEY `country_fr_idx` (`country_fr`),
      UNIQUE KEY `country_seo_idx` (`country_seo`),
      KEY `idx_country_fr_seo` (`country_fr_seo`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    Résultat du EXPLAIN (photo )

    Nom : query.png
Affichages : 36
Taille : 270,9 Ko

    Merci

  5. #5
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 155
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 155
    Points : 45 237
    Points
    45 237
    Par défaut
    Il est normal que vous ayez des problèmes de performances. Votre table est TRES mal modélisée. Aucun des principes de modélisation (formes normales notamment) n'ont été appliqués.
    Dès lors mettre des index constituera un puits sans fond à objectif perdu….
    Commencez par normaliser votre table et vous aurez peut être même pas besoin de l'indexer….

    Lisez l'article que j'ai écrit à ce sujet :
    https://blog.developpez.com/sqlpro/p...mances_petites

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  6. #6
    Membre actif
    Inscrit en
    septembre 2004
    Messages
    433
    Détails du profil
    Informations forums :
    Inscription : septembre 2004
    Messages : 433
    Points : 257
    Points
    257
    Par défaut
    Dur à encaisser, du coup je me rends compte que je dois tout revoir, mais merci beaucoup !!!

  7. #7
    Expert éminent sénior

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    4 910
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 4 910
    Points : 13 846
    Points
    13 846
    Billets dans le blog
    1
    Par défaut
    Effectivement, les tables sont obèses et c'est symptomatique d'une modélisation bâclée.
    N'hésitez pas à poser des questions dans le forum consacré à la modélisation qui se trouve ici :
    https://www.developpez.net/forums/f6...sation/schema/

  8. #8
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 280
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 280
    Points : 12 017
    Points
    12 017
    Par défaut
    Bonjour,


    vous pourriez toutefois créer un index sur la colonne adate de la table t_game2, en ajoutant éventuellement les colonnes de cette table utilisées dans la requête afin de rendre l'index couvrant (et donc minimiser l'impact de la mauvaise modélisation)

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 155
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 155
    Points : 45 237
    Points
    45 237
    Par défaut
    Autrement dit remodéliser à l'envers en faisant enfler la volumétrie globale….

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Discussions similaires

  1. Réponses: 2
    Dernier message: 18/10/2017, 17h00
  2. [MySQL-5.0] Activer le "log-queries-not-using-indexes"
    Par Chochal dans le forum Administration
    Réponses: 4
    Dernier message: 07/12/2015, 20h05
  3. pointeur char - value computed is not used
    Par n0mad dans le forum Débuter
    Réponses: 6
    Dernier message: 08/03/2009, 19h21
  4. Error: Could not use '(unknown)';
    Par Identifiant dans le forum Servlets/JSP
    Réponses: 3
    Dernier message: 22/01/2008, 21h46
  5. tutoriel : Database.Open-Could not use, file already in use
    Par MARTIN Gérard dans le forum XMLRAD
    Réponses: 2
    Dernier message: 04/05/2005, 11h56

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo