changeset 281:dd8629e6d67e

Ugly workaround to speed up count-new
author HIROSE Yuuji <yuuji@gentei.org>
date Tue, 02 Aug 2016 22:09:06 +0859
parents 2d2d32a9bb80
children e6c1cbe4ffcc
files s4-funcs.sh
diffstat 1 files changed, 9 insertions(+), 6 deletions(-) [+]
line wrap: on
line diff
--- a/s4-funcs.sh	Tue Aug 02 14:43:20 2016 +0859
+++ b/s4-funcs.sh	Tue Aug 02 22:09:06 2016 +0859
@@ -2574,11 +2574,14 @@
   if [ -n "$DT_CHLD" ]; then
     _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
     cntall="(select count($_i) from $_t where $_i=a.id)"
+    # XXX: Dirty workaround for slow subquery of acclog
+    presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
+            SELECT * FROM acclog WHERE user='$user' and tbl='$2';"
     cntnew="(select count(val) from ${_t}_s where key='ctime' \
-	and id in (select id from $_t where $_i=a.id) \
-	and val > coalesce((select time from acclog where \
-			    user='$user' and tbl='$2' and tblrowid=a.rowid),\
-		    '1970-01-01'))"
+        and id in (select id from $_t where $_i=a.id) \
+        and val > coalesce((select time from myacclog where \
+                            tblrowid=a.rowid),\
+                    '1970-01-01'))"
     cnt="$cntnew as '新着', $cntall as '総数',"
     dt_class=" td2r td3r dumpblogs"
   fi
@@ -2606,12 +2609,12 @@
 	max(case key when 'owner'
 	 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos
  from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
-  err SQL=`echo "$sql"`
+  err SQL="`echo \"$presql$sql\"|tr -d '\n'`"
   cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>,"
 <div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
 <div class="dumptable">
 <table class="b$dt_class">
-`sq -header -cmd ".mode $1" $db "$sql"`
+`sq -header -cmd ".mode $1" $db "$presql$sql"`
 </table>
 </div> <!-- dumptable -->
 </div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->

yatex.org