Optimiser une requête avec des conditions binaires

Question

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 ?

 

 

Comments

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 ...

Answer 1

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

Comments

oui bien vu, il faut aussi faire de ipmax et ipmin des index simples.

---

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 )

Answer 2

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.

 

 

Comments

oui, c'est exactement ça. Bonne illustration de explain pour l'optimisation