MySQL not using primary key as index

MohammedCherkaoui
April 24, 2023
259 views
1 vote
2 Answers

I checked other questions with the same issue, But they seem not to have a primary key or the number of rows to examine is low which lets MySQL decides not to use an index.
In my case it is different, Joining with "dossiers" table adds 10 seconds for query to execute.
This is EXPLAIN result:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	statuts	const	PRIMARY	PRIMARY	4	const	1	Using index; Using temporary; Using filesort
1	SIMPLE	dossiers	ALL	PRIMARY	NULL	NULL	NULL	92801	Using where
1	SIMPLE	dt	ref	PRIMARY,dossierId,statutId	dossierId	4	btecma5_jaddad.dossiers.dossierId	1	Using index condition; Using where
1	SIMPLE	dtst	ref	dossierTribunalId,statutProcedureId,utilisateurId	dossierTribunalId	4	btecma5_jaddad.dt.dossierTribunalId	4	Using index condition; Using where
1	SIMPLE	sp	eq_ref	PRIMARY	PRIMARY	4	btecma5_jaddad.dtst.statutProcedureId	1	Using where
1	SIMPLE	utilisateurs	eq_ref	PRIMARY	PRIMARY	4	btecma5_jaddad.dtst.utilisateurId	1	Using where
1	SIMPLE	dtt	ref	dossierTribunalId	dossierTribunalId	4	btecma5_jaddad.dt.dossierTribunalId	6	Using where
1	SIMPLE	dttl	eq_ref	PRIMARY	PRIMARY	4	btecma5_jaddad.dtt.dossierTribunalTransfertLotId	1	Using where

And this the query: ( dossiers.estSoumis is not indexed but it doesn’t make much difference )

SELECT 
      dt.utilisateurId dossierPhysiqueUtilisateurId,
      COUNT(dttl.dossierTribunalTransfertLotId) totalTransferts,
      traceDate dateSoumission,
      dtst.utilisateurId creationUtilisateurId,
      CONCAT(utilisateurs.prenom,' ',utilisateurs.nom) nomAgent 
 FROM dossiers_tribunaux_statuts_traces dtst
 JOIN dossiers_tribunaux dt USING(dossierTribunalId)
 LEFT JOIN dossiers_tribunaux_transferts dtt
        ON(dt.dossierTribunalId = dtt.dossierTribunalId)
 LEFT JOIN dossiers_tribunaux_transferts_lots dttl
        ON(dttl.dossierTribunalTransfertLotId = dtt.dossierTribunalTransfertLotId
           AND dttl.statut = 1)
 JOIN dossiers dossiers USING(dossierId)
 JOIN utilisateurs utilisateurs
        ON(utilisateurs.utilisateurId = dtst.utilisateurId)
 JOIN statuts_procedures sp USING(statutProcedureId)
 JOIN statuts statuts ON(statuts.statutId = sp.statutId)
WHERE statuts.statutId = 1 
  AND dt.statutId != 211 
  AND dossiers.estSoumis = 1 
  AND dtst.utilisateurId IN(3, 4, 5, 6, 7, 8, 9, 10, 62, 63) 
GROUP BY dt.dossierTribunalId;

EDIT: Tables structure and indexes added:

--
-- Table structure for table `dossiers`
--

CREATE TABLE `dossiers` (
  `dossierId` int(10) UNSIGNED NOT NULL,
  `numeroDossier` int(6) NOT NULL,
  `typeDossier` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:masse;2:grand',
  `clientId` int(11) DEFAULT NULL,
  `dateCreation` datetime NOT NULL,
  `creationUtilisateurId` int(11) NOT NULL,
  `estSoumis` tinyint(1) NOT NULL DEFAULT 0,
  `dateSoumission` datetime DEFAULT NULL,
  `anneeDossier` int(4) NOT NULL,
  `estArreter` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:provisoire,2:definitif',
  `arretDate` datetime DEFAULT NULL,
  `arretUtilisateurId` int(11) DEFAULT NULL,
  `arretAnnulationDate` datetime DEFAULT NULL,
  `arretAnnulationUtilisateurId` int(11) DEFAULT NULL,
  `piecesClientsScan` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:pas encore;1:scannés',
  `piecesClientsScanDate` datetime DEFAULT NULL,
  `lotFacturationId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `dossiers_tribunaux`
--

CREATE TABLE `dossiers_tribunaux` (
  `dossierTribunalId` int(10) UNSIGNED NOT NULL,
  `dossierTribunalParentId` int(11) DEFAULT NULL,
  `numeroTribunalSequence` int(6) DEFAULT NULL,
  `numeroTribunalCodeProcedure` int(4) NOT NULL,
  `numeroTribunalAnnee` int(4) NOT NULL,
  `procedureId` int(11) NOT NULL,
  `dossierId` int(10) NOT NULL,
  `statutId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `dossiers_tribunaux_statuts_traces`
--

CREATE TABLE `dossiers_tribunaux_statuts_traces` (
  `dossierTribunalStatutTraceId` int(10) UNSIGNED NOT NULL,
  `dossierTribunalId` int(11) NOT NULL,
  `statutProcedureId` int(11) NOT NULL,
  `traceDate` datetime NOT NULL,
  `utilisateurId` int(11) NOT NULL,
  `dossierActionId` int(11) DEFAULT NULL,
  `besoinScan` tinyint(1) NOT NULL DEFAULT 0,
  `estScanne` tinyint(1) NOT NULL DEFAULT 0,
  `codeOperationScan` int(10) DEFAULT NULL,
  `estExcluOperationScan` tinyint(1) NOT NULL DEFAULT 0,
  `exclusionOperationScanUtilisateurId` int(11) NOT NULL,
  `exclusionOperationScanDate` datetime DEFAULT NULL,
  `extraCommentaire` text DEFAULT NULL,
  `extraCommentaireDerniereMaj` datetime DEFAULT NULL,
  `statutAffichable` tinyint(1) NOT NULL DEFAULT 1,
  `crediteurId` int(11) DEFAULT NULL,
  `crediteurDerniereMaj` datetime DEFAULT NULL,
  `estIgnoreAudit` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `dossiers_tribunaux_transferts`
--

CREATE TABLE `dossiers_tribunaux_transferts` (
  `dossierTribunalTransfertId` int(10) UNSIGNED NOT NULL,
  `dossierTribunalTransfertLotId` int(11) NOT NULL,
  `dossierTribunalId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `dossiers_tribunaux_transferts_lots`
--

CREATE TABLE `dossiers_tribunaux_transferts_lots` (
  `dossierTribunalTransfertLotId` int(10) UNSIGNED NOT NULL,
  `deUtilisateurId` int(11) NOT NULL,
  `aUtilisateurId` int(11) NOT NULL,
  `dateTransfert` datetime NOT NULL,
  `statut` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:en attente de validation;1: validé',
  `dateValidation` datetime DEFAULT NULL,
  `numeroLot` int(6) NOT NULL,
  `anneeLot` int(4) NOT NULL,
  `orderType` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:lotId;2:numeroDossier',
  `validationUtilisateurId` int(11) DEFAULT NULL COMMENT 'Pour diagnotiquer un probleme'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `statuts`
--

CREATE TABLE `statuts` (
  `statutId` int(10) UNSIGNED NOT NULL,
  `intituleStatut` varchar(100) NOT NULL,
  `intituleStatutParent` varchar(100) NOT NULL,
  `ordreAffichage` smallint(6) NOT NULL DEFAULT 1,
  `intituleStatutArabe` varchar(100) NOT NULL,
  `informationsComplementaires` text DEFAULT NULL,
  `labelCouleur` varchar(50) NOT NULL DEFAULT 'info',
  `typeStatut` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:Action à faire;2: Réponse tribunal',
  `estImportable` tinyint(1) NOT NULL DEFAULT 0,
  `majStatutsId` text DEFAULT NULL,
  `estRepetable` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `statuts_procedures`
--

CREATE TABLE `statuts_procedures` (
  `statutProcedureId` int(10) UNSIGNED NOT NULL,
  `statutId` int(11) DEFAULT NULL,
  `procedureId` int(11) NOT NULL,
  `intituleAction` varchar(255) DEFAULT NULL,
  `nombreJoursRetard` int(2) DEFAULT NULL,
  `nombreJoursRetardAlert` int(2) DEFAULT NULL,
  `afficherTableauBord` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `utilisateurs`
--

CREATE TABLE `utilisateurs` (
  `utilisateurId` int(10) UNSIGNED NOT NULL,
  `nom` varchar(100) NOT NULL,
  `prenom` varchar(100) NOT NULL,
  `email` varchar(200) NOT NULL,
  `motPasse` varchar(100) NOT NULL,
  `usergroup` int(11) NOT NULL DEFAULT 1,
  `peutLierFacture` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `dossiers`
--
ALTER TABLE `dossiers`
  ADD PRIMARY KEY (`dossierId`),
  ADD KEY `creationUtilisateurId` (`creationUtilisateurId`),
  ADD KEY `arretUtilisateurId` (`arretUtilisateurId`),
  ADD KEY `arretAnnulationUtilisateurId` (`arretAnnulationUtilisateurId`),
  ADD KEY `clientId` (`clientId`),
  ADD KEY `numeroDossier` (`numeroDossier`);

--
-- Indexes for table `dossiers_tribunaux`
--
ALTER TABLE `dossiers_tribunaux`
  ADD PRIMARY KEY (`dossierTribunalId`),
  ADD KEY `procedureId` (`procedureId`),
  ADD KEY `dossierId` (`dossierId`),
  ADD KEY `banqueId` (`banqueId`),
  ADD KEY `employeurId` (`employeurId`),
  ADD KEY `statutId` (`statutId`),
  ADD KEY `suiviId` (`suiviId`),
  ADD KEY `utilisateurId` (`utilisateurId`),
  ADD KEY `lotFacturationId` (`lotFacturationId`);
ALTER TABLE `dossiers_tribunaux` ADD FULLTEXT KEY `contratsId` (`contratsId`);
ALTER TABLE `dossiers_tribunaux` ADD FULLTEXT KEY `adversairesId` (`adversairesId`);

--
-- Indexes for table `dossiers_tribunaux_statuts_traces`
--
ALTER TABLE `dossiers_tribunaux_statuts_traces`
  ADD PRIMARY KEY (`dossierTribunalStatutTraceId`),
  ADD KEY `dossierTribunalId` (`dossierTribunalId`),
  ADD KEY `statutProcedureId` (`statutProcedureId`),
  ADD KEY `dossierActionId` (`dossierActionId`),
  ADD KEY `utilisateurId` (`utilisateurId`);

--
-- Indexes for table `dossiers_tribunaux_transferts`
--
ALTER TABLE `dossiers_tribunaux_transferts`
  ADD PRIMARY KEY (`dossierTribunalTransfertId`),
  ADD KEY `dossierTribunalTransfertLotId` (`dossierTribunalTransfertLotId`),
  ADD KEY `dossierTribunalId` (`dossierTribunalId`);

--
-- Indexes for table `dossiers_tribunaux_transferts_lots`
--
ALTER TABLE `dossiers_tribunaux_transferts_lots`
  ADD PRIMARY KEY (`dossierTribunalTransfertLotId`),
  ADD KEY `deUtilisateurId` (`deUtilisateurId`),
  ADD KEY `aUtilisateurId` (`aUtilisateurId`),
  ADD KEY `validationUtilisateurId` (`validationUtilisateurId`),
  ADD KEY `dossiers_transfert_idx_autilisateurid_dossiertribunal` (`aUtilisateurId`,`dossierTribunalTransfertLotId`);

--
-- Indexes for table `statuts`
--
ALTER TABLE `statuts`
  ADD PRIMARY KEY (`statutId`);

--
-- Indexes for table `statuts_procedures`
--
ALTER TABLE `statuts_procedures`
  ADD PRIMARY KEY (`statutProcedureId`);

--
-- Indexes for table `utilisateurs`
--
ALTER TABLE `utilisateurs`
  ADD PRIMARY KEY (`utilisateurId`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `dossiers`
--
ALTER TABLE `dossiers`
  MODIFY `dossierId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dossiers_tribunaux`
--
ALTER TABLE `dossiers_tribunaux`
  MODIFY `dossierTribunalId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dossiers_tribunaux_statuts_traces`
--
ALTER TABLE `dossiers_tribunaux_statuts_traces`
  MODIFY `dossierTribunalStatutTraceId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dossiers_tribunaux_transferts`
--
ALTER TABLE `dossiers_tribunaux_transferts`
  MODIFY `dossierTribunalTransfertId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dossiers_tribunaux_transferts_lots`
--
ALTER TABLE `dossiers_tribunaux_transferts_lots`
  MODIFY `dossierTribunalTransfertLotId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `statuts`
--
ALTER TABLE `statuts`
  MODIFY `statutId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `statuts_procedures`
--
ALTER TABLE `statuts_procedures`
  MODIFY `statutProcedureId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utilisateurs`
--
ALTER TABLE `utilisateurs`
  MODIFY `utilisateurId` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

Answers

- RickJames
- April 24, 2023 at 8:51 pm
- 0 votes
0
There are many reasons why the PK may not be used. But first, let’s add some ‘composite’ indexes that are likely to help with performance:
```
dt:  INDEX(statutId, dossierTribunalId,  utilisateurId, dossierId)
dtst:  INDEX(utilisateurId, dossierTribunalId,  statutProcedureId)
    and DROP  KEY `utilisateurId`
dtt:  INDEX(dossierTribunalId,  dossierTribunalTransfertLotId)
    and DROP  KEY `dossierTribunalId`
dossiers:  INDEX(estSoumis, dossierId)
```
(Many of those are also "covering", as indicated by "Using index".)

If estSoumis is not selective enough, my recommendation for dossiers may not help much.

dossiers_tribunaux_transferts is structured like a many-to-many mapping table. If that is its intent, then drop the surrogate id and add a 2-column PK and a 2-column INDEX in the opposite order. See Many-to-many
Login or Signup to reply.

- PaulMaxwell
- April 25, 2023 at 2:59 am
- 0 votes
0
Try adding these 2 compound indices:
```
CREATE INDEX idx_dossierTribunalTransfertLotId_statut ON dossiers_tribunaux_transferts_lots(dossierTribunalTransfertLotId, statut);
CREATE INDEX idx_dossierId_estSoumis ON dossiers(dossierId, estSoumis);
```
dossiers_tribunaux_transferts_lots(dossierTribunalTransfertLotId, statut) might help speed up the LEFT JOIN between dossiers_tribunaux_transferts and dossiers_tribunaux_transferts_lots, as well as the filtering condition dttl.statut = 1 in the ON clause.

dossiers(dossierId, estSoumis) might help speed up the JOIN between dossiers_tribunaux and dossiers, as well as the filtering condition dossiers.estSoumis = 1 in the WHERE clause.

edit: I note you may have already tried the reverse order of the columns and as estSoumis is tinyint that order may perform better. I had this answer sitting in edit without posting and left it overnight by mistake, then posted and saw the other answer.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.