Discussion :

[tyarcaouen]

J'essaie d'optimiser une requête SQL très lente malgré les indexes créés.
La requête ci dessous prend 22s
La table stat contient 724 000 lignes et stats_ip 74 000 lignes.
Une idée pour l'optimiser ?

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
SELECT b.pays, COUNT(b.pays) FROM stats a, stats_ip b WHERE b.pays <> '' AND b.ip = a.ip AND a.date > now() - INTERVAL 12 month GROUP BY b.pays ORDER BY 2 DESC limit 5;
 
 
-- Structure de la table `stats` 724 000 lignes
CREATE TABLE `stats` (
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `page` int(11) NOT NULL DEFAULT 0,
  `ip` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `syst` int(11) NOT NULL DEFAULT 0,
  `navi` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- Index pour la table `stats`
ALTER TABLE `stats`
  ADD KEY `index_date` (`date`) USING BTREE,
  ADD KEY `index_ip` (`ip`),
  ADD KEY `index_date_ip` (`date`,`ip`),
  ADD KEY `index_ip_date` (`ip`,`date`);
COMMIT;
 
-- Structure de la table `stats_ip` 74 000 lignes
CREATE TABLE `stats_ip` (
  `ip` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `ville` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `region` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `pays` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED;
 
-- Index pour la table `stats_ip`
ALTER TABLE `stats_ip`
  ADD PRIMARY KEY (`ip`) USING BTREE,
  ADD UNIQUE KEY `index_ip_ville` (`ip`,`ville`),
  ADD UNIQUE KEY `index_ip_region` (`ip`,`region`),
  ADD UNIQUE KEY `index_ip_pays` (`ip`,`pays`);
COMMIT;
 
 
explain SELECT b.pays, COUNT(b.pays) FROM stats a, stats_ip b WHERE b.pays <> '' AND b.ip = a.ip AND a.date > now() - INTERVAL 12 month GROUP BY b.pays ORDER BY 2 DESC limit 5;
 
 
1	SIMPLE	b	index	PRIMARY,index_ip_ville,index_ip_region,index_ip_pays	index_ip_pays	919	NULL	71922	Using where; Using index; Using temporary; Using filesort	
1	SIMPLE	a	ref	index_date,index_ip,index_date_ip,index_ip_date	index_ip_date	152	tyarcaouen.b.ip	5	Using where; Using index

[SQLpro]

Commencez par modéliser correctement vos tables...
1) un VARCHAR(50) est ce qu'il y a de pire en matière de clé. Remplacez le par un entier ne dépssant pas la longueur du mot du processeur (64 bits). Votre VARCHAR(50) coute au pire 150 octets du fait que MySQL/MariaDB utilise un UTF8 imbécile avec systématiquement 3 octets par caractères, soit 1 200 octets, ce qui nécessite 19 passes dans le processeur pour lire une clé soit 38 passes pour faire une jointure...
2) évitez la date 0 qui est une imbécilité propre à MySQL MariaDB
3) il n'y a pas de clé primaire dans votre table "stats"

Enfin, modifiez votre moteur pour qu'il soit en mode STRICT au niveau du SQL. Sinon, vos résultats de requêtes peuvent être incorrect. Dans le mode par défaut, les erreurs sont passées sous silence et MySQL / MariaDB renvoie n'importe quoi....

Une fois ceci corrigé on pourra parler indexation, mais à première vue aucun des index ne satisferons votre requête !

A +

[escartefigue]

Et aussi : évitez les mots réservés SQL et les caractères spéciaux (accents, ligatures, cédilles...) dans les noms d'objets. "DATE" est un mot réservé SQL, à remplacer par date_stat par exemple.
Ça permet de se débarrasser des quotes inversées (alt-gr + 7) dans les requêtes et ça facilite les analyses.

[Michel.Priori]

Bonjour,

On peut essayer d'écrire la requête autrement :

Code :

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

1
2
3
4
5
6
7
SELECT b.pays, COUNT(b.pays) 
FROM stats a, stats_ip b 
WHERE b.pays <> '' 
AND b.ip = a.ip 
AND a.date > now() - INTERVAL 12 month 
GROUP BY b.pays 
ORDER BY 2 DESC limit 5;

Note : le mot clé JOIN a été introduit dans la norme SQL de 1992. Vous devriez envisager de vous mettre à jour.


Avec IN :

Code :

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

1
2
3
4
5
6
SELECT b.pays, COUNT(b.pays) 
FROM stats_ip b
WHERE b.pays <> '' 
AND b.ip IN (SELECT a.ip FROM stats a where a.date > now() - INTERVAL 12 month)
GROUP BY b.pays 
ORDER BY 2 DESC limit 5;

Avec Exists :

Code :

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

1
2
3
4
5
6
SELECT b.pays, COUNT(b.pays) 
FROM stats_ip b
WHERE b.pays <> '' 
AND EXISTS (SELECT a.ip FROM stats a where b.ip = a.ip AND a.date > now() - INTERVAL 12 month )
GROUP BY b.pays 
ORDER BY 2 DESC limit 5;