Discussion :

[gotcha5832]

Bonjour à tous,

Je souhaite faire un gros update mais si ma requete passe en select, dès que je la transforme en update.
Lorsque je lance La requete depuis phpmyadmin, mysql prends 100% de cpu, fini en timeout.

Et à ce moment la requete n'a pas updater toutes les entrées.

Voici ma requete

Code :

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

1
2
3
4
5
6
7
UPDATE `phplist_user_user` 
SET `confirmed` = 0
WHERE 
	(`entered` < '2017-01-01 00:00:01' OR`entered` > '2017-02-20 00:00:01' )
	AND `id` NOT IN (SELECT `userid` FROM `phplist_usermessage` WHERE `viewed` IS NOT NULL GROUP BY `userid`)
	AND `id` IN (select `userid` FROM `phplist_usermessage` group by `userid` having count(*) > 10) 
	AND `id` NOT IN (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5);

Comment vous y prendriez vous?

[gotcha5832]

A votre avis est il préférable d'améliorer la requete, passer par 3 jointure plus que des sous requete
ou passer par un requete intermédiaire?

[CinePhil]

Si je lis bien votre requête, vous voulez mettre confirmed à 0 les user qui sont entrés avant le 01/01/2017 ou après le 20/02/2017 et qui n'ont pas eu des messages vus (le GROUP BY ne sert ici à rien) et qui ont plus de 10 messages et qui ne sont pas dans la liste 5. C'est ça ?

Essayez comme ça :

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
UPDATE phplist_user_user u1
SET confirmed = 0
WHERE id IN
(
	SELECT u.id
	FROM phplist_user_user u
	INNER JOIN phplist_listuser l ON l.userid = u.id
	INNER JOIN phplist_usermessage m ON m.userid = u.id
	WHERE l.listid = 5
	GROUP BY u.id
	HAVING COUNT(*) > 10
)
	AND NOT EXISTS
	(
		SELECT *
		FROM phplist_usermessage um
		WHERE um.userid = u1.id
			AND um.viewed IS NOT NULL
	)

[gotcha5832]

Merci @CinePhil
Effectivement vous l'avez bien comprise.

Dans votre code il manque les dates.
Donc je suppose:

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
UPDATE phplist_user_user u1
SET confirmed = 0
WHERE 	(u1.`entered` < '2017-01-01 00:00:01' OR u1.`entered` > '2017-02-20 00:00:01' )
AND id IN
(
	SELECT u.id
	FROM phplist_user_user u
	INNER JOIN phplist_listuser l ON l.userid = u.id
	INNER JOIN phplist_usermessage m ON m.userid = u.id
	WHERE l.listid = 5
	GROUP BY u.id
	HAVING COUNT(*) > 10
)
	AND NOT EXISTS
	(
		SELECT *
		FROM phplist_usermessage um
		WHERE um.userid = u1.id
			AND um.viewed IS NOT NULL
	)

Les jointures sont elles moins gourmante que les sous requete?

[CinePhil]

D'une manière générale, les jointures sont plus rapides que les requêtes corrélées sauf dans le cas de l'utilisation du NOT EXISTS.

Avec une requête corrélée dans le WHERE, le SGBD va devoir exécuter la requête pour chaque ligne de la requête principale. Il est plus rapide de joindre les tables et de restreindre (WHERE) le résultat de la jointure, même si la jointure s'opère sur toutes les lignes satisfaisant à la condition de jointure, même sur celles qu'on élimine ensuite avec le WHERE.
Mais avec NOT EXIST, l'analyse de chaque ligne s'arrête dès que la condition NOT EXISTS est rompue. Ainsi, dans votre cas, pour un user donné, dès que le SGBD va trouver un message vu, il va passer à l'utilisateur suivant, même s'il reste des dizaines de messages à examiner pour le premier utilisateur.

[Artemus24]

Salut gotcha5832.

Il nous manque la structure de vos tables afin de mieux comprendre ce que vous essayez de faire. Un jeu d'essai aurait été le bien venu !
Je n'aime pas trop raisonner dans le vide car on ne fait qu'interpréter ou supposer comment cela fonctionne.

Analysons ce que vous faites :

Code :

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

AND   `id` NOT IN (SELECT `userid` FROM `phplist_usermessage` WHERE `viewed` IS NOT NULL GROUP BY `userid`)

1) On ne sait rien de la relation qui existe entre votre table "phplist_user_user" et la table "phplist_usermessage".
Est-ce une injection ? Une surjection ? Une bijection ? Une inclusion ? Cela risque de produire des résultats différents !

Ensuite, vous utilisez une double négation !
Dans le select, vous récupérez tous les "userid" dont les "viewed" ne sont pas à NULL.
Comme l'indique CinePhil, le group by ne sert à rien, sinon à faire perdre du temps à l'exécution.

Ensuite, dans le "and", vous cherchez à récupérer tous les "id" qui ne sont pas présents dans votre select.
A bien comprendre, ce sont ceux qui ont "viewed" à NULL mais aussi ceux qui ne sont pas référencés dans la table "phplist_usermessage".

Code :

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

AND   `id`     IN (SELECT `userid` FROM `phplist_usermessage`                            GROUP BY `userid` having count(*) > 10)

2) Cette fois-ci, le "group by" à son utilité. Vous désirez récupérer les "userid" ayant plus de 10 lignes présentes dans la table "phplist_usermessage".

3) Je pense qu'il faut reformuler vos accès à la table "phplist_usermessage", en plusieurs jointures :
a) récupérer les "id" qui ont une ligne présente dans la table "phplist_usermessage" avec "viewed" à NULL.
b) récupérer les "id" qui ne sont pas référencées dans la table "phplist_usermessage".
c) récupérer les "id" qui ont plus de dix lignes dans la table "phplist_usermessage".
C'est une simple supposition, comme je l'ai indiqué ci-dessus, car on ne sait rien de la relation qui existe entre vos deux tables.

4) Vous avez deux problèmes avec la colonne "entered".
D'une part, vous utilisez un "or" ce qui va rendre l'index non sargable sur la colonne "entered".
Je constate que vous n'êtes pas très logique dans la façon de formuler votre intervalle.
Si vous indiquez "`entered` < '2017-01-01 00:00:01'", je comprends que votre intervalle inclue aussi '2017-01-01 00:00:00'.
Alors quand vous formulez "`entered` > '2017-02-20 00:00:01'", cela n'est pas logique de procéder ainsi.
Vous devez plutôt écrire "`entered` > '2017-02-19 23:59:59'"

D'autre part, je ne comprends pas non plus pourquoi vous procédez ainsi ???
Et surtout que ce n'est pas ainsi que l'on écrit un intervalle de date.

Décomposez cette colonne en "date" et en time".
Puis faites ceci : "`entered_date` not between '2017-01-01' and '2017-02-20'", qui est sargable !

Code :

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

AND `id` NOT IN (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5);

5) le mieux est d'utiliser "exist" ou "not exist", ce qui revient au même.

6) Pourquoi ne pas utiliser une view afin d'alléger votre update ?

Ce qui donne :

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
drop view if exist `vue`;
 
create view `vue` as
 
  select  userid
    from  `phplist_listuser`
   WHERE  `listid` <> 5
   union
  select  userid
    from  `phplist_usermessage`
group by  `userid`
  having  count(*) > 10
   union
  select  userid
    from  `phplist_usermessage`
   where  `viewed` IS NULL
   union
  select  id as userid
    from  `phplist_user_user` as t1
   where  not exist (select  1
                       from  `phplist_usermessage` as t2
                      where  t2.userid = t1.id
                    )
order by userid;
 
update      `phplist_user_user` as t1
inner join  `vue`               as t2
        on  t2.userid = t1.id
       set  t1.confirmed = 0
     where  `entered` not between '2017-01-01 00:00:00' and '2017-02-19 23:59:59';

@+

[tbc92]

L'autre question essentielle, c'est les ordres de grandeur.
Normalement, si l'update fonctionne, il doit modifier combien de lignes environ ? Et accessoirement, combien y-a-t-il de lignes dans la table phplist_user_user ?

Si tu fais tout simplement : update phplist_user_user set confirmed = 0
donc modifier toutes les lignes,
Ca aboutit, ou non ?

[gotcha5832]

Merci beaucoup pour votre aide et vos informations,
N'ayant aucune formation, et pur autodidacte des BDD, celà m'aide grandement à progresser.



Pour répondre à vos questions

@Artemus24

Il nous manque la structure de vos tables afin de mieux comprendre ce que vous essayez de faire. Un jeu d'essai aurait été le bien venu !

Comment puis je vous transférer celà?

1) On ne sait rien de la relation qui existe entre votre table "phplist_user_user" et la table "phplist_usermessage".
Est-ce une injection ? Une surjection ? Une bijection ? Une inclusion ? Cela risque de produire des résultats différents !

je ne connaissais pas ce principe, mais si j'ai bien compris c'est une injection puisque je seul lien étant
"phplist_user_user" id = userid "phplist_usermessage".

b) récupérer les "id" qui ne sont pas référencées dans la table "phplist_usermessage".
c) récupérer les "id" qui ont plus de dix lignes dans la table "phplist_usermessage".

je ne comprends pas comment un id peut à la fois ne pas être référencé et à la plus de dix fois?

4) Vous avez deux problèmes avec la colonne "entered".
D'une part, vous utilisez un "or" ce qui va rendre l'index non sargable sur la colonne "entered".

Merci grace à vous je découvre la notion de sargable

D'autre part, je ne comprends pas non plus pourquoi vous procédez ainsi ???
Et surtout que ce n'est pas ainsi que l'on écrit un intervalle de date.

Décomposez cette colonne en "date" et en time".

Voulez vous dire splitter la colonne en 2 ou juste la requete

Puis faites ceci : "`entered_date` not between '2017-01-01' and '2017-02-20'", qui est sargable !

J'ai cru lire Que le Not était par défault non sargable?
()

AND `id` NOT IN (SELECT `userid` FROM `phplist_listuser` WHERE `listid` = 5);

5) le mieux est d'utiliser "exist" ou "not exist", ce qui revient au même.

A la lecture de cette article sur la notion de sargable je comprends qu'il est préférable de Choisir EXIST plutot que IN
Mais dans le cas présent on est sur un NOT IN et le NOT EXIST est tout aussi non-sargable?

Je ne comprend pas votre remarque

utiliser "exist" ou "not exist", ce qui revient au même

car les deux ne reviens pas au meme

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
phplist_user_user
 
| id  |     … |
___________
|  1  |    …  |
|  2  |    …  |
|  3  |    …  |
|  4  |    …  |
___________
 
phplist_listuser
______________
| userid | listid |
______________
|  1      |    1  |
|  2      |    1  |
|  2      |    5  |
|  3      |    5  |
_____________

Dans le cas ci dessus

Code :

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

AND `id` NOT IN (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5);

ou

Code :

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

AND `id` NOT EXIST (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5);

vas extraire:
id = {1,4}

Hors

Code :

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

AND `id` EXIST (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` != 5);

vas extraire:
id = {1}
Non??

6) Pourquoi ne pas utiliser une view afin d'alléger votre update ?

Bonne question

L'autre question essentielle, c'est les ordres de grandeur.
Normalement, si l'update fonctionne, il doit modifier combien de lignes environ ? Et accessoirement, combien y-a-t-il de lignes dans la table phplist_user_user ?

phplist_user_user+- 50 000
L'update ~50%

[tbc92]

Quand on fait :

Code :

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

AND `id` NOT IN (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5)

Comment procède le moteur ? Il prépare une liste ( je dirais même un listing) avec la liste de tous les userid qui ont listid = 5, puis pour chaque candidat id venant de la table phplist_user_user, il recherche dans ce listing si id y figure ou non. Mais cette recherche est lente, il fait comme nous quand on cherche un nom dans un listing, mais dans un listing non trié !!! , et donc on lit tous les noms un par un.

Peut-être même que la préparation du listing en question, le moteur doit la faire plein de fois (un fois avant chaque recherche), mais on va être optimiste, et on va dire qu'il ne la fait qu'une fois.

Par contre quand on fait

Code :

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

AND Not exist ( select * from `phplist_listuser` WHERE `listid` = 5 and 'userid' = 'id')

, le moteur a plus d'outil à disposition... enfin peut-être.
Pour chaque id candidat, le moteur va aller lire dans la table phplist_listuser et s'il y a les index adéquats, il va pouvoir trouver très vite si la ligne cherchée existe ou non.
Le fait qu'on ait un NOT ici ne pose pas de problème.
L'histoire du NOT qui n'est pas sargable, c'est un autre débat

Quand on lui demande de lire toutes les lignes de phplist_user_user et de traiter uniquement les lignes dans un intervalle de date, le moteur sait utiliser un index (si la colonne date est indexée), et il sait lire uniquement la portion de dates utile. Si on lui demande de traiter les dates en dehors d'un intervalle, ça peut etre plus galère.

Mais ici, tes volumes sont ""faibles"". Lire 50000 lignes, pour constater qu'on aurait pu lire uniquement 20000 ou 30000, ce n'est pas une catastrophe.

Si tu veux rester avec des IN ou NOT IN, tu peux déjà faire comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE `phplist_user_user` 
SET `confirmed` = 0
WHERE 
	(`entered`     not between  '2017-01-01 00:00:01'  and    '2017-02-20 00:00:01' 
	AND `id` IN
          (
            select `userid` FROM `phplist_usermessage` group by `userid` having count(*) > 10
            minus 
              (         SELECT `userid` FROM `phplist_usermessage` WHERE `viewed` IS NOT NULL GROUP BY `userid`    
              union  SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5
              )
          ) 
	 ;

Ainsi le moteur prépare un listing avec les userid qui vérifient les 3 conditions voulues, et pour chaque id de phplist_user_user, il doit lire un seul listing, au lieu de lire 3 listings. Et en plus il va devoir lire un listing plus petit que les listings du 1er scénario !

C'est important, parce que cette étape de lecture des listings, il doit la faire un paquet de fois.

Mais la solution encore plus efficace, si les indexes adéquats existent, c'est de passer par NOT EXIST.

[Artemus24]

Salut gotcha5832.

Comment puis je vous transférer celà?

Pour chaque table, vous passez par l'export de phpmyadmin afin de récupérer le jeu d'essai.
Ensuite, vous déposez vos fichiers en utilisant dans les "Options supplémentaires" du message, la partie nommée "Pièces jointes".

Je voie que vous ne connaissez pas vos classiques en mathématique.
--> http://www.bibmath.net/dico/index.ph...injection.html

Ceci définie la jointure "phplist_user_user" id = userid "phplist_usermessage" et ne nous renseigne pas sur la composition de vos deux tables.

je ne comprends pas comment un id peut à la fois ne pas être référencé et à la plus de dix fois?

Les deux lignes b) et c) sont disjointes ou si vous ne comprenez pas le terme disjointe, ce sont deux cas différents qui n'ont aucun rapport entre eux.

Merci grâce à vous je découvre la notion de sargable

Le terme sargable signifie que le filtrage va utiliser un index pour restreindre les accès aux lignes concernées.
Le fait de passer par un index prend un peu de temps en plus pour sélectionner la ligne.
Dans le cas contraire, il y a un balayage total de la table !
Il y a un seuil à ne pas dépasser en terme de performance entre passer par l'index et le baylage.
D'où un problème de performance.

Voulez vous dire splitter la colonne en 2 ou juste la requete

La réponse est dans ce que j'ai dit :

Décomposez cette colonne en "date" et en time".

Votre intervention va se faire dans la table afin de "splitter" la colonne "entered", en deux colonnes "entered_date" et "entered_time".

J'ai cru lire Que le Not était par défault non sargable?

Il ne faut pas être catégorique car cela dépend de la condition du filtrage et comment cela est programmé dans un SGBD.

Le principe repose sur le balayage de index et l'application du filtrage.
La condition repose que sur la colonne "entered" qui est l'index.
Vous appliquez la condition et celle-ci est soit vraie ou soit fausse.
Dans le cas où elle est vraie, on va lire la ligne dans la table.
Il ne faut pas faire comme SQLPRO le généraliser sans comprendre comment cela fonctionne réellement dans le SGBD.
Dans mon exemple, le NOT est sargable, car la condition dépend que de l'index et seulement de l'index.

A la lecture de cette article sur la notion de sargable je comprends qu'il est préférable de Choisir EXIST plutot que IN

Cela dépend de la quantité de valeur que vous récupérez dans un "in".
Pour ma part, j'utilise le "in" uniquement avec une liste de valeur en dure et limitée à quelques valeurs, comme ci-après : "test in (1, 2, 3, 4)".
Si vous récupérez plusieurs centaines de valeurs, vous aurez un problème de stockage qui peut faire planter votre requête.
Dans les autres cas, j'utilise la clause "exist".

Je ne comprends pas votre remarque

utiliser "exist" ou "not exist", ce qui revient au même

Je reprends votre exemple :

Code :

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

AND `id` NOT IN (SELECT `userid`  FROM `phplist_listuser` WHERE `listid` = 5);

Cela peut se traduire en :

Code :

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

AND NOT EXIST (SELECT 1 FROM phplist_listuser as t2 WHERE t2.listid = 5 AND t2.userid = t1.id);

mais aussi en :

Code :

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

AND EXIST (SELECT 1 FROM phplist_listuser as t2 WHERE t2.listid <> 5 OR t2.userid <> t1.id);

Ma remarque concerne l'équivalence des écritures !

Mais dans le cas présent on est sur un NOT IN et le NOT EXIST est tout aussi non-sargable?

Encore une fois, il faut comprendre comment fonctionne le "not in" et le "not exist" et ce qui se passe quand c'est sargable.
Disons que parfois quand c'est sargable, c'est plus long en terme de performance que de faire un simple balayage de la table.
C'est pourquoi à juste titre, tbc92 dit : "L'autre question essentielle, c'est les ordres de grandeur." (avec une belle erreur de syntaxe, car on écrit : "quelles sont les ordres de grandeur ?").

@+

[gotcha5832]

Merci à vous deux
@ Artemus24
Voici un export, j'espère que c'est cela que vous attendiez.

@tbc92
Merci de votre proposition, mais sauf erreur de ma part minus n'existe pas sous mysql?

[escartefigue]

Merci de votre proposition, mais sauf erreur de ma part minus n'existe pas sous mysql?

Minus = Except

[escartefigue]

Il ne faut pas faire comme SQLPRO le généraliser sans comprendre comment cela fonctionne réellement dans le SGBD.
Dans mon exemple, le NOT est sargable, car la condition dépend que de l'index et seulement de l'index.

[escartefigue]

Tiens artemus24 est passé par la pour mettre des moins 1 ; le bougre a la rancune tenace

Réflechissez artémus : au lieu de dénigrer les experts, apprenez comment fonctionnent les bases de données relationnelles cela vous évitera de proférer (et de répéter) des énormités

[Artemus24]

Salut Escartfigue.

Arrêtez de vous qualifier d'expert alors que vous n'êtes même pas capable de nous fournir un exécutable d'une requête en mysql !
Et ces -1 vous les méritez parce que vous dénigrez sans démontrer quoi que ce soit.
A l'inverse de vous, je pense avoir bien plus d'expérience en tant que développeur pour comprendre comment fonctionne les SGBD.
Et j'ai assez bidouiller en tant administrateur DB2 à l'exploitation pour avoir une quelconque expérience en ce domaine.

Je ne me qualifie pas en tant qu'expert pour me valoriser comme vous le faite. Ça démontre surtout un manque d'assurance dans votre vie.
A l'inverse de vous, je suis capable de fournir un exécutable aussi bien de MySql, MariaDB, FireBird et aussi Microsoft SQL Server, même si je connais moins bien ce SGBDR.
Pourquoi ? Car ces SGBDR sont installés sur mon ordinateur et je prends le temps de faire les tests.
Vous n'intervenez que pour étaler votre connaissance livresque sans donner la moindre preuve de ce que vous affirmer.

Je prends comme exemple le "except" ou vous prétendez que cela existe sous MySql.
Oui, c'est votre message #12 et nous sommes dans le forum consacré à MySql.

A vous l'expert de pacotille, je vais vous apprendre que sous MySql, ce "except" n'existe pas !
C'est une différence entre deux tables et cela se codifie sous MySql avec un "left outer join" comme dans mon exemple ci-après :

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
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test1`
--------------
 
--------------
CREATE TABLE `test1`
( `val`   integer unsigned  not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test1` (`val`) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
--------------
 
--------------
select * from `test1`
--------------
 
+-----+
| val |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+
--------------
DROP TABLE IF EXISTS `test2`
--------------
 
--------------
CREATE TABLE `test2`
( `val`   integer unsigned  not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test2` (`val`) values (4),(5),(6),(7)
--------------
 
--------------
select * from `test2`
--------------
 
+-----+
| val |
+-----+
|   4 |
|   5 |
|   6 |
|   7 |
+-----+
--------------
select           t1.val
           from  test1 as t1
left outer join  test2 as t2
             on  t2.val = t1.val
          where  t2.val is null
       order by  t1.val
--------------
 
+-----+
| val |
+-----+
|   1 |
|   2 |
|   3 |
|   8 |
|   9 |
|  10 |
+-----+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Expliquez moi, vous qui savez mieux que tout le monde, pourquoi le "NOT" n'est pas sargable ?
Autrement dit, une condition est sargable parce qu'elle est vrai, mais dans le cas contraire, soit quand elle est fausse, elle ne l'est plus.

Cela me rappelle une conversation où vous prétendiez haut et fort, que j'avais tort au sujet des performances sur une requête qui pratiquait la recherche du minimum.
Vous avez fait le test sous Microsoft SQL Server et vous avez constaté que j'avais raison.
Mais comme vous ne reconnaissez pas avoir eu tort, vous avez tout fait pour me démontrer que j'étais dans l'erreur.
J'appelle ça faire preuve de mauvaise foi.

J'attends votre répondre monsieur l'expert !
Et j'aimerai que personne d'autre vienne aider sans sa démarche monsieur escartefigue afin que sa réponse vienne de lui seule.
Qu'il me fasse la démonstration de son affirmation avec MySql et pas avec Microsoft SQL Server !

@+

[Artemus24]

Salut gotcha5832.

Je vous ai demandé un jeu d'essai conforme à votre problème et non pas un export vide !
Comment voulez-vous que je résolve votre problème si je ne me retrouve pas dans les mêmes conditions que vous ?

@+

[escartefigue]

Contrairement a vous je ne regarde pas mon nombril, quand je parlais d'expert il ne s'agissait pas de moi !

Car quand vous osez dire ceci :

Il ne faut pas faire comme SQLPRO le généraliser sans comprendre comment cela fonctionne réellement dans le SGBD.
Dans mon exemple, le NOT est sargable, car la condition dépend que de l'index et seulement de l'index.

Vous dénigrez un expert, et venant d'un ignare de votre espèce, c'est plus que comique, c'est grotesque !

[Artemus24]

Salut Escartefigue.

Vous dénigrez un expert, et venant d'un ignare de votre espèce, c'est plus que comique, c'est grotesque !

Vous avez une très haute estime de votre personne.

Vous connaissez sûrement cette parabole : "voir la paille dans l’œil du voisin et ne pas voir la poutre dans le sien".
Elle vous concerne car quand on est aveugle à toutes suggestions, on ne peut voir la vérité quand elle est évidente.

Mais ce qui est grotesque, c'est affirmer sans savoir de quoi l'on parle, monsieur je sais tout !
Pour se rendre compte; il suffit de faire le test sous MySql, comme ci-après :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`   integer  unsigned  auto_increment not null  primary key,
  `val`  smallint unsigned  not null,
  index `idx` (`val`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`val`) values (15),(17),(33),(42),(45),(48),(52),(56),(62),(68),(72),(84),(88)
--------------
 
--------------
select * from `test`
--------------
 
+----+-----+
| id | val |
+----+-----+
|  1 |  15 |
|  2 |  17 |
|  3 |  33 |
|  4 |  42 |
|  5 |  45 |
|  6 |  48 |
|  7 |  52 |
|  8 |  56 |
|  9 |  62 |
| 10 |  68 |
| 11 |  72 |
| 12 |  84 |
| 13 |  88 |
+----+-----+
--------------
DROP TABLE IF EXISTS `flag`
--------------
 
--------------
CREATE TABLE `flag`
( `id`   integer  unsigned  auto_increment  not null  primary key,
  `val`  smallint unsigned  not null,
  index `idx` (`val`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `flag` (`val`) values (17),(45),(52),(56),(72),(84)
--------------
 
--------------
select * from `flag`
--------------
 
+----+-----+
| id | val |
+----+-----+
|  1 |  17 |
|  2 |  45 |
|  3 |  52 |
|  4 |  56 |
|  5 |  72 |
|  6 |  84 |
+----+-----+
--------------
select  *
  from  `test` as t1
 where  not exists (select 1 from `flag` as t2 where t2.val = t1.val)
--------------
 
+----+-----+
| id | val |
+----+-----+
|  1 |  15 |
|  3 |  33 |
|  4 |  42 |
|  6 |  48 |
|  9 |  62 |
| 10 |  68 |
| 13 |  88 |
+----+-----+
--------------
explain
select  *
  from  `test` as t1
 where  not exists (select 1 from `flag` as t2 where t2.val = t1.val)
--------------
 
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
|  1 | PRIMARY            | t1    | NULL       | index | NULL          | idx  | 2       | NULL        |   14 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | ref   | idx           | idx  | 2       | base.t1.val |    1 |   100.00 | Using index              |
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Et que voyons-nous dans cet exemple ? Que le "NOT EXISTS" est sargable !
Et oui, c'est exactement le contraire de ce que vous avez affirmé précédemment dans un de vos stupides messages de dénigrement.
Au cas où vous ne seriez pas capable de comprendre l'explain, le fait que mysql indique "Using Index" suffit à indiquer que le test est sargable.
Et je dirais même plus, que la colonne "type" indique comment va se faire le filtrage : par "index".

Je pense que SQLPRO est assez grand pour se défendre lui-même et n'a pas besoin d'un héraut pour s'adresser à moi !

@+

[SQLpro]

Au cas où vous ne seriez pas capable de comprendre l'explain, le fait que mysql indique "Using Index" suffit à indiquer que le test est sargable.
Et je dirais même plus, que la colonne "type" indique comment va se faire le filtrage : par "index".

Comme d'habitude vous dites n'importe quoi. Le fait d'utiliser un index ne suffit pas à le dire "sargable". En effet un index peut être accédé de différentes manières et au moins 2 :

• par un "scan" (balayage) et dans ce cas cela ne diffère pas d'une lecture ligne à ligne de table, mais il est souvent moins consommateur de balayer un index qu'une table, l'index étant réputé être moins gros que la table...
• par un "seek" (recherche) et dans ce cas il est dit sargable...

Il existe bien entendu d'autres méthodes d'accès aux index, par exemple le LOOKUP (multi seek) dans SQL Server...

A +

[Artemus24]

Salut SQLPRO.

Le fait d'utiliser un index ne suffit pas à le dire "sargable".

La définition de sargable (search + argument + able), qui consiste à passer par un index n'est pas suffisante.
Je suis resté à cette définition et peu importe la méthode de l'accès à l'index.
D'ailleurs, c'est ce que l'on retrouve ici comme définition.

Au moins, votre définition a le mérite de mettre court à toute polémique ! C'est de faire un accès directe (seek) à l'index.

J'ai trouvé un sujet où Escartefigue s'est retrouvé dans le même cas que moi au sujet de l'opérateur différent.
No comment !

P.S.: je ne suis pas rancunier, SQLPRO, je vous ai mis un +1 !

@+