Optimiser une requête avec des conditions binaires

+3 votes
6,973 vues
question 4 Janvier 17 dans Sql Phpmyadmin par saintjoseph (192 points)  

TIP est une table d'ips interdits à 3 colonnes de 140000 lignes :   : ip , masque , oui/non = interdit/autorisé. ip est la clé primaire

Il faut la croiser avec une table IPAV d'ips à valider à une colonne, ipc , ipc est la primaire.

Select * from IPAV inner join TIP on ipc & masque = ip ; // 140000 lignes scrutées

Pourtant ipc et ip sont des clés. J'ai mis masque en clé d'index mais ça ne change pas, la requête scrute toujours toute la table.

Comment faire pour valider les lignes de la table IPAV sans ce gâchis ?

 

 

commentée 4 Janvier 17 par ladmin (2,272 points)  
oui, encore une bonne question. La réponse est dans le fonctionnement des index. Rien de magique, des règles simples. Je répondrai ce soir si personne ne le fait avant ...

2 Réponses

+3 votes
réponse 4 Janvier 17 par paco (436 points)  
les index sont pour index=valeur ou index like valeur.

Je suppose que ce n'est pas masque mais la longueur du masque. Exemple 1.2.3.4/24 veut dire 1.2.3.XX . XX a pour longueur 32-24 bits

Ici , en supposant que les paires (ips,masques) ne se chevauchent pas, il faut changer la table avec ( ipmin,ipmax), ipmin=ipc et impax = ipc + ( 1 << ( 32-masque) ) -1

et faire : select * from table where ip <= ipmax and ip>=ipmin
commentée 5 Janvier 17 par anvers (269 points)  
oui bien vu, il faut aussi faire de ipmax et ipmin des index simples.
commentée 5 Janvier 17 par anvers (269 points)  
si certaines paires s'emboitent, faire un sort by ipmin desc, ipmax asc  pour faire la plus petite tenaille et limit 0,1  pour ne retrouver que le bon résultat, s'il y en a. Il faut des données au pire hiérarchisées ( l'une incluse dans l'autre ou pas d'intersection )
+1 vote
réponse 7 Janvier 17 par saintjoseph (192 points)  
editée 7 Janvier 17 par ladmin

Pour compléter la réponse de Paco que je remercie, je rapporte une remarque du SAV

Soit une table de 10.000 lignes avec un index numérique num de valeurs quelqconques

EXPLAIN SELECT * FROM `table` WHERE num>=18236 limit 0,1

Réponse de Mysql : key=num, rows=1 , Extra = Using index condition

puis les mêmes avec une modification apparemment anodine de la condition

EXPLAIN SELECT * FROM `table` WHERE num+1>=18237 limit 0,1

Réponse de Mysql : key=NULL, rows=10000 , Extra = Using where

Pour utiliser un index , il ne faut pas le recomposer dans un calcul.

Si num est un index,  num+1 et même num*1 ne sont pas des index ! Pas plus que lorsque plusieurs index sont combinés dans une opération. Les expressions ne sont pas simplifiées, c'est au programmeur de le faire.

 

 

commentée 7 Janvier 17 par ladmin (2,272 points)  
oui, c'est exactement ça. Bonne illustration de explain pour l'optimisation
Feel free to ask and answer in english

Bienvenue sur les FAQ de 123.fr, posez vos questions ou répondez à celles des autres usagers.

...