Bonjour,

Je fais une requête de ce type et ça met des plombes à s'effectuer :
SELECT DISTINCT url FROM logs WHERE ip LIKE('192.169.%') AND url NOT IN(SELECT url FROM logs WHERE ip LIKE('245.345.%'));

En fait j'ai l'impression que à chaque fois que j'utilise IN / NOT IN c'est ultra lent.
Y a t il moyen d'optimiser ce genre de requête ?
La base contient 14 champs et 104 000 lignes. J'abandonne la requête au bout d'1h par ce que ça me saoule.

Merci !

PS : voici un explain de la requête :
 
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                        |
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|  1 | PRIMARY            | logs  | ALL  | NULL          | NULL | NULL    | NULL | 104352 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | logs  | ALL  | NULL          | NULL | NULL    | NULL | 104352 | Using where                  |
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+

Combien de résultats retournent les deux requêtes :
SELECT DISTINCT url FROM logs WHERE ip LIKE('192.169.%')

et

SELECT url FROM logs WHERE ip LIKE('245.345.%')

et sont-elles rapides ?
Si N est le nombre de résultats de la première et M est le nombre de résultats de la secondes, potentiellement, tu fais NxM comparaisons.

Ajoute un index sur la colonne ip
Ensuite, je ferai une jointure

SELECT distinc url FROM logs l1 , LEFT JOIN logs l2 ON l1.url = l2.ip 
WHERE l1.ip LIKE '192.169.%' AND l2.ip LIKE '245.345.%' 
AND l2. url IS NULL /*pour dire qu'on veut les enrigrestrement de l1 qui n'existent pas dans l2*/

vanquishV12:
tu as un post ici, il y a un mois environ sur les optimisations...
100000 lignes , c'est rien...
tu as dans ta requete, en gros tout se qui fait ramer une requete sql:
-----------------------------------------------------------------------------
SELECT DISTINCT url FROM logs WHERE ip LIKE('192.169.%') AND url NOT IN(SELECT url FROM logs WHERE ip LIKE('245.345.%'));
-----------------------------------------------------------------------------

Distinct ... regarde en le remplaçant par un group by... si tu gagnes pas du temps
apres quoi , tu fais des sélections de type like... pas bon non plus
Mais cela doit venir de ta non indexation de ta table  sur la jointure et ta selection...
donc Ip et /ou  url...
à voir avec ton analyseur SQL...

Flo850 : pourquoi faire une requete left outer join
avec un test de la valeur à null (de la seconde jointure)
NB: le not /in correspond à un exception join...

donc ça donne simplement ça :

select distinct a.url from logs as a
exception join logs as b
on a.url = b.url
where a.ip like '192....%' and
          b.ip like '255....%'
(+/- group by a.url sans le distinct)

Guillaume

P.S.: En reprenant ta requête, on a:  je veux les urls dont l'ip est 192.165... et je ne veux pas les url dont l'ip est '245.354'

select distinct url from log where ip like '192.165.%' and ip not like '245.345.%'
devrait aussi donner le résultat, non?

D'accord avec tout le reste, d'ailleurs j'ai appris un truc, je connaissais pas les exception join, ca existe sous Oracle ca?

Pour ta remarque a la fin, ca ne donne pas le meme resultat. Par exemple si tu as:
URL1 - 192.165.0.1
URL1 - 245.354.0.1

La requete initiale ne retourne pas URL1 mais la tienne si.

Edit: bien sur c'est si les URLs ne sont pas uniques, mais bon vu qu'il y a un DISTINCT je suppose qu'elle ne le sont pas sinon on peut aussi virer le distinct direct.

 
Exception join n'est pas standard. La plupart des SGBD le refuseront (Oracle/SQL Server/My SQL/etc).
Ca doit être que sur AS/400 ce genre de trucs non ?

Moi je tenterais, dans l'ordre:
 
1) Ajout d'un index sur le champ ip (obligatoire)
 
2) Remplacer le NOT IN par un NOT EXISTS, peut-être moins coûteux :

 
3) Si toujours pas bon, tenter le NOT EXISTS avec un index sur l'url

Et les instructions LIKE ne posent pas de problème de performance si les champs sont correctement indexés et que le wildcard est à la fin comme ici.

index sur url + ip pour gagner du temps. Il faut placer en premier le plus restrictif des deux

Merci j'ai ajouté un index sur IP
et j'ai changé la requête pour :
SELECT DISTINCT url FROM logs WHERE ip LIKE('xxxx.%') AND url IN(SELECT url FROM logs l WHERE ip NOT LIKE('xxxx.%') AND logs.url = l.url);
 
C'est passé de plusieurs heures à 30 secondes !
 
Merci bcp pour tous vos messages, je vais bien les relire. Toute alimentation de ce topic m'intéressera bcp

Tu peux remplacer le distinct par un group by.

C'est fait mais ça n'accélère pas

 
Bizarre de préconiser ça, quand on apprend le sql on apprend l'inverse : ne pas utiliser de Group by inutiles et utiliser distinct à la place. Ne garder le Group by que pour son vrai rôle à savoir pour les fonctions d'agrégation.
 
Tu as vu des cas où tu gagnais en performance  

Sur MySql oui, le group by est en général plus rapide qu'un DISTINCT.
 
L'algo est le suivant pour Group by : je conserve uniquement la première ligne de toutes celles qui ont les mêmes valeurs pour les champs spécifiés dans le group by.
 
Pour le distinct, faut faire des comparaisons entre toutes les lignes.
 
Après, un distinct sur un seul champ, c'est peut-être pas dit que tu y gagnes (et ça dépend aussi de la taille du jeu de données). Mais quand tu spécifies plusieurs champs, ça devient intéressant. En plus, certains SGBD tolèrent que tu ne mettes pas dans le group by tous les champs présents dans le select, chose que tu ne peux pas faire avec le distinct.
 
Au passage, gpl73, dans son premier post, faisait la même proposition.

Intéressant, je serais curieux de savoir dans quel cas ça se produit. Tu as des exemples ?
 
D'après les avis que je lis en ligne, c'est l'inverse : les gens conseillent de préférer DISTINCT à GROUP BY quand c'est possible http://stackoverflow.com/questions [...] y-in-mysql .
 
Je vois pas trop pourquoi tu aurais moins de comparaisons sur le GROUP BY. Et d'ailleurs si la logique du GROUP BY est plus rapide (ce dont je doute, je pense qu'un DISTINCT est plus "facile" à faire) pourquoi MySql n'utilise pas cette logique en arrière plan lorsqu'on retire des doublons ?

Sinon, il n'y a pas longtemps sous Oracle, j'ai eu un cas ou utiliser un filtre sur ROW_NUMBER() WITHING GROUP(ORDER BY xxx) = 1 (en gros) etait plus performant qu'un DISTINCT ou un GROUP BY.
Mais bon c'etait un truc assez tordu.

Au fait, merci, mais sur mon MySql, exception join ne passe pas

Parmi tout ce qui a été proposé la requête de Yonel est très largement plus rapide que les autres.
Ca prend quand même toujours 30 secondes
Je ne peux pas mettre d'index sur "url", c'est par ce que c'est un champ text ?

Je pense que le type 'text' n'est pas adapté pour stocker des URL. Ca doit effectivement poser des problèmes de rapidité car il est assez coûteux de faire des conditions du genre monTexte1 = monTexte2 dans un WHERE.
 
A ta place je tenterais de changer le type text en VARCHAR. Tu peux par exemple utiliser VARCHAR(10000) ou VARCHAR(65535). Ou alors puisque tu as des données, regarde la longueur max stockée, et utilise le double de celle-ci (tout en dépassant pas 65535). http://stackoverflow.com/questions [...] -for-a-url
 
Une fois que tu as ça tu testes. Ce sera peut-être déjà un peu plus rapide. Ensuite tu tentes avec un index qui combine url + ip, comme propose ddr555. Normalement c'est le plus sélectif en premier, donc probablement dans l'ordre (url, ip). Mais ça se tente aussi dans l'ordre (ip, url) pour voir.

Je sais pas si ça a été dit mais le NOT IN consomme beaucoup plus qu'un LEFT JOIN si tu as des index.

Merci. J'ai rajouté des index sur IP et URL (pas les deux ensemble, il le faut ?) et j'ai mis varchar 1000 au lieu de text pour les URL
De la requête du début qui prenait 1h30 je suis passé à ... moins d'une seconde  
 
Du coup le NOT IN me va bien
 
Tu as un "guide" pour expliquer comment transformer une requête not in en left join ?
 
Et une IN ?

la requete que j'ai mis au debut remplace le not in par une jointure

ok merci, mais pour le IN ?

Pour le IN, en reprenant la requête de flo850, c'est juste une jointure toute bête :
 

 
Il faudrait sans doute que tu lises au sujet des self-join : http://www.mysqltutorial.org/mysql-self-join/
Ou plus généralement les jointures tout court : http://www.mysqltutorial.org/mysql-inner-join.aspx

 
Du coup comme le sujet m'intrigait je me suis renseigné.
 
En MySQL, avant la version 5.6, lorsqu'on utilisait le GROUP BY il y avait un tri implicite des données. Donc à l'époque un GROUP BY était potentiellement plus lent qu'un DISTINCT puisqu'on faisait un tri supplémentaire.
Ce mécanisme est devenu deprecated depuis la version 5.6 : http://www.tocker.ca/2013/10/21/he [...] l-5-6.html
 
En plus, en MySQL, ils ont ajouté un truc qui n'est pas du SQL standard, à savoir que tu n'es pas obligé d'avoir toutes les colonnes du SELECT dans le GROUP BY : http://dev.mysql.com/doc/refman/5. [...] sions.html
 
Donc:
 
- Avant MySQL 5.6, toujours privilégier le DISTINCT par rapport au GROUP BY (à cause du sort en plus qui ralentit forcément). Ou alors utiliser ORDER BY NULL, technique bien moche ( ), préconisée par certains pour désactiver le sort implicit du GROUP BY.
 
- Après MySQL 5.6:  
 

• Si les doublons concernent toutes les colonnes du SELECT, privilégier le DISTINCT pour respecter le standard SQL  

• Si les doublons concernent moins de colonnes que celles présentes dans le SELECT, privilégier le GROUP BY.

Tu n'es pas obligé mais si tu ne le fais pas ça fait de la merde.

 
Ca m'étonne pas, à mon avis c'est pas pour rien si c'est interdit par la quasi totalité des SGBD. M'enfin si ça fait gagner en perf dans certaines conditions pourquoi pas.
 
Ce qui me gêne plus c'est de voir des gens qui apprenent le SQL qui ne suivent pas cette logique et qui du coup ne comprennent pas comment marche un GROUP BY (pas sur ce post mais on en croise régulièrement).

Merci pour cette recherche approfondie. J'aurai appris des trucs grâce à toi

En termes d'optim, les adresses IPv4 doivent être stockées en tant que INT UNSIGNED, puis en utilisant INET_ATON / INET_NTOA.
Voir la prez Join-fu part1, slide 7 pour exemple.
 
Je serais curieux de voir combien tu gagnes avec ça par rapport aux LIKE multiples.

Merci à tous ce topic est vraiment très enrichissant

Et le nombre de dév qui savent pas utiliser un group by à cause de mysql c'est effarant.  
 
Du coup faudrait mettre ce sujet obligatoire avant chaque post sur mysql !

C'est ce que fait Piwik : il stocke de cette manière les IP v4.
 
Je pense que l'intérêt par rapport au like, c'est que ça doit être plus rapide de faire des opérations mathématiques/logiques sur des INT plutôt que sur des chaînes.
 
Perso, pour stocker certaines données représentant des droits d'accès ou des valeurs de cases à cocher représentant des options, j'utilisais des entiers ou chaque bit représentait une valeur : 0 -> pas coché, 1 -> coché. Du coup, pour trouver des enregistrements qui avaient certaines options activées (ou pas), c'était très rapide puisqu'il suffisait de faire un masque binaire et de l'appliquer avec un opérateur logique AND ou OR. C'était surtout intéressant, je pense, en terme de gain de temps, quand il fallait trouver des enregistrements qui avaient l'une des options proposées dans une longue liste : ça évitait de faire un IN ou des OR (ça, ça tue le SGBD des OR); ça évite aussi de faire des jointures si on a choisi de représenter les options cochées des enregistrement par une relation de type 0-n
 
Edit : et du coup, c'est aussi facile d'indexer les enregistrements qui ont les mêmes options cochées

 
Ma question sur "je serais curieux de savoir combien tu gagnes...", c'était vraiment dans le cas particulier de la requête de vanquish, pas spécialement pour le cas général
 

C'est toujours difficile à quantifier; ça dépend de la taille du jeu de données, du nb de jointures entre les tables (s'il y en a), de comment sont répartis les indexes, de comment tu as tuné le fichier de conf de mysql... L'optimisation de requêtes c'est tout une question de dosage, un peu comme la cuisine

surtout que dans une requête ce qui prend beaucoup de temps c'est la lecture disque, pas trop les tests de comparaison qui sont négligeables par rapport au reste du processus. on ne perd pas grand chose au final. sur un long processus ça peut nécessiter de gagner le maximum. pour une requête unique qui prend un seconde on s'en balance complètement ...
 
un index sur les deux colonnes était la chose à faire, après l'ordre des colonnes du plus restrictif au moins restrictif permet de gagner un peu, mais reste je pense peu important.

Je profite de votre expertise pour vous demander comment faire et comment faire VITE pour avoir ce genre de résultats :
J'ai deux champs
Adresse ; Nom de magasin
Je voudrais savoir les Noms de magasins qui sont utilisés par plusieurs adresses (et avoir la liste par exemple tel nom de magasin est utilisé par telles adresses)
 
Merci !!!

Fait un autre sujet.