Changeset 94722 in spip-zone


Ignore:
Timestamp:
Jan 23, 2016, 2:57:58 PM (3 years ago)
Author:
cedric@…
Message:

Perf issue : forcer les sous-requetes en uncorrelated http://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why#6157797

Location:
_plugins_/acces_restreint/trunk
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • _plugins_/acces_restreint/trunk/paquet.xml

    r93547 r94722  
    22        prefix="accesrestreint"
    33        categorie="auteur"
    4         version="3.13.6"
     4        version="3.14.0"
    55        etat="dev"
    66        compatibilite="[3.0.0;3.1.*]"
  • _plugins_/acces_restreint/trunk/public/accesrestreint.php

    r87182 r94722  
    139139function accesrestreint_articles_accessibles_where($primary, $_publique=''){
    140140        # hack : on utilise zzz pour eviter que l'optimiseur ne confonde avec un morceau de la requete principale
    141         $where =  "array('NOT IN','$primary','('.sql_get_select('zzza.id_article','spip_articles as zzza',".accesrestreint_rubriques_accessibles_where('zzza.id_rubrique','',$_publique).",'','','','',\$connect).')')";
     141        $where =  "array('NOT IN','$primary','(SELECT * FROM('.sql_get_select('zzza.id_article','spip_articles as zzza',".accesrestreint_rubriques_accessibles_where('zzza.id_rubrique','',$_publique).",'','','','',\$connect).') AS subquery)')";
    142142        $where = "array('AND', $where, ".accesrestreint_objets_accessibles_where('articles', $primary, 'not', $_publique).")";
    143143        return $where;
     
    153153function accesrestreint_breves_accessibles_where($primary, $_publique=''){
    154154        # hack : on utilise zzz pour eviter que l'optimiseur ne confonde avec un morceau de la requete principale
    155         $where = "array('NOT IN','$primary','('.sql_get_select('zzzb.id_breve','spip_breves as zzzb',".accesrestreint_rubriques_accessibles_where('zzzb.id_rubrique','',$_publique).",'','','','',\$connect).')')";
     155        $where = "array('NOT IN','$primary','(SELECT * FROM('.sql_get_select('zzzb.id_breve','spip_breves as zzzb',".accesrestreint_rubriques_accessibles_where('zzzb.id_rubrique','',$_publique).",'','','','',\$connect).') AS subquery)')";
    156156        $where = "array('AND', $where, ".accesrestreint_objets_accessibles_where('breves', $primary, 'not', $_publique).")";
    157157        return $where;
     
    167167function accesrestreint_syndic_articles_accessibles_where($primary, $_publique=''){
    168168        # hack : on utilise zzz pour eviter que l'optimiseur ne confonde avec un morceau de la requete principale
    169         $where = "array('NOT IN','$primary','('.sql_get_select('zzzs.id_syndic','spip_syndic as zzzs',".accesrestreint_rubriques_accessibles_where('zzzs.id_rubrique','',$_publique).",'','','','',\$connect).')')";
     169        $where = "array('NOT IN','$primary','(SELECT * FROM('.sql_get_select('zzzs.id_syndic','spip_syndic as zzzs',".accesrestreint_rubriques_accessibles_where('zzzs.id_rubrique','',$_publique).",'','','','',\$connect).') AS subquery)')";
    170170        $where = "array('AND', $where, ".accesrestreint_objets_accessibles_where('syndic', $primary, 'not', $_publique).")";
    171171        return $where;
     
    192192        $where = "array('OR',$where,sql_in('zzzf.objet',\"'rubrique','article','breve'\",'NOT',\$connect))";
    193193       
    194         return "array('IN','$primary','('.sql_get_select('zzzf.id_forum','spip_forum as zzzf',array($where),'','','','',\$connect).')')";
     194        return "array('IN','$primary','(SELECT * FROM('.sql_get_select('zzzf.id_forum','spip_forum as zzzf',array($where),'','','','',\$connect).') AS subquery)')";
    195195}
    196196
     
    212212       
    213213        $where = "array('OR',
    214         array('IN','$primary','('.sql_get_select('zzzd.id_document','spip_documents_liens as zzzd',array($where),'','','','',\$connect).')'),
    215         array('NOT IN','$primary','('.sql_get_select('zzzd.id_document','spip_documents_liens as zzzd','','','','','',\$connect).')')
     214        array('IN','$primary','(SELECT * FROM('.sql_get_select('zzzd.id_document','spip_documents_liens as zzzd',array($where),'','','','',\$connect).') AS subquery)'),
     215        array('NOT IN','$primary','(SELECT * FROM('.sql_get_select('zzzd.id_document','spip_documents_liens as zzzd','','','','','',\$connect).') AS subquery)')
    216216        )";
    217217       
Note: See TracChangeset for help on using the changeset viewer.