s4
changeset 337:48d0b6c4de65
Revise SQL for article search drastically for speed.
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Sat, 22 Oct 2016 22:58:10 +0859 |
| parents | 08165f6b7828 |
| children | 291a3eeed05e |
| files | s4-blog.sh s4-funcs.sh |
| diffstat | 2 files changed, 45 insertions(+), 35 deletions(-) [+] |
line diff
1.1 --- a/s4-blog.sh Sat Oct 22 00:39:53 2016 +0859 1.2 +++ b/s4-blog.sh Sat Oct 22 22:58:10 2016 +0859 1.3 @@ -462,24 +462,25 @@ 1.4 searchart() { 1.5 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC 1.6 kwdgrp="" 1.7 + authcond="" 1.8 if [ -z "$kwd" ]; then 1.9 echo "検索語を指定してください" | html p; return 1.10 fi 1.11 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then 1.12 # Like '#1234', assume as artID 1.13 rowid=$((${kwd#\#} + 0)) # Force to be a number 1.14 - kc="a.rowid = $rowid" 1.15 + kc="ar.rowid = $rowid" 1.16 else 1.17 for k in `echo "$kwd" | sed "s/'/''/g"`; do 1.18 if expr x"$k" : 'x@[1-9][][0-9]*-[][0-9:-]*$' >/dev/null 1>&2; then 1.19 # Like '@2016-10-10', expands to "s.ctime GLOB '@2016-10-10'" 1.20 ctime=${k#@} 1.21 # Not sure GROUP BY a.blogid is comfortable for searchers...? 1.22 - kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'" 1.23 + kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'" 1.24 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results 1.25 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then 1.26 ctime=`date +%F` 1.27 - kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'" 1.28 + kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'" 1.29 else 1.30 kc=$kc${kc:+" AND "}"content LIKE '%$k%'" 1.31 fi 1.32 @@ -499,7 +500,8 @@ 1.33 fi 1.34 elif { author=`getpar author`; test -n "$author"; }; then 1.35 atptn=`sqlquotestr $author` 1.36 - kc="$kc${kc:+ AND }author=$atptn" 1.37 + #kc="$kc${kc:+ AND }author=$atptn" 1.38 + authcond="WHERE author=$atptn" 1.39 if isuser $author; then 1.40 echo "(`linkhome $author` さんの書き込みからの検索)" | html p 1.41 fi 1.42 @@ -509,41 +511,48 @@ 1.43 # blog: id=blog-id, author=LeaderAuthor 1.44 # blog_s: id=blog-id, key='title', val='BLOG-TITLE' 1.45 # WANT: blog-ROWid,article-id,val(TEXT) 1.46 - sql="`sql4readableblogs` -- Extract user-readable blogs 1.47 - SELECT b.rid||'#'||x.id as '', 1.48 - b.title as TITLE, 1.49 - substr(x.ctime, 0, 11) as DATE, 1.50 - substr(x.content, 0, 78) as TEXT 1.51 - FROM (SELECT blg.rid,blg.*,bs.val as title 1.52 - FROM readableblogs blg JOIN blog_s bs 1.53 - ON blg.id=bs.id AND bs.key='title') b 1.54 - JOIN 1.55 - (SELECT a.id, 1.56 - a.blogid, 1.57 - s.ctime, 1.58 - s.text /* || ' ' || m.filenames */ content 1.59 - FROM article a 1.60 - JOIN 1.61 - (SELECT id, /* Create [id, ctime, text] from article_s */ 1.62 - max(CASE key WHEN 'ctime' THEN val END) ctime, 1.63 - max(CASE key WHEN 'text' THEN val END) text 1.64 - FROM article_s GROUP BY id) s 1.65 - /* 1.66 - JOIN 1.67 - (SELECT id, group_concat(val) filenames 1.68 - FROM article_m 1.69 - WHERE type LIKE 'file:%' 1.70 - GROUP BY id) m */ 1.71 - ON a.id=s.id /* AND a.id=m.id */ WHERE $kc$kwdgrp) x 1.72 - ON b.id=x.blogid 1.73 - WHERE b.id IN (SELECT id FROM blog_s $cond) 1.74 - ORDER by DATE DESC, TITLE, x.ctime;" 1.75 + sql2="`sql4readableblogs` -- Extract user-readable blogs 1.76 +-- 0.3sec 1.77 +WITH artsm AS ( 1.78 + SELECT a.id,ctime, text || ' ' || coalesce(files, '') content 1.79 + FROM article a 1.80 + LEFT JOIN 1.81 + (SELECT ars.id, ctime, text, coalesce(files, '') files 1.82 + FROM (SELECT id, 1.83 + max(CASE key WHEN 'ctime' THEN val END) ctime, 1.84 + max(CASE key WHEN 'text' THEN val END) text 1.85 + FROM article_s 1.86 + GROUP BY id) ars 1.87 + LEFT JOIN 1.88 + (SELECT id, group_concat(val) files 1.89 + FROM article_m 1.90 + WHERE type LIKE 'file:%' 1.91 + GROUP BY id) arm 1.92 + ON ars.id=arm.id 1.93 + ) ar 1.94 + ON a.id=ar.id 1.95 +), ar AS ( 1.96 + SELECT article.rowid, article.blogid, article.id, ctime, content 1.97 + FROM article JOIN artsm ON article.id=artsm.id 1.98 + $authcond 1.99 +), bl AS ( 1.100 + SELECT blg.rid, blg.*, blog_s.val TITLE 1.101 + FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title' 1.102 +) 1.103 +SELECT bl.rid||'#'||ar.id '', 1.104 + bl.title TITLE, 1.105 + substr(ctime, 0, 11) DATE, 1.106 + substr(content, 0, 78) TEXT 1.107 +FROM ar JOIN bl 1.108 + ON ar.blogid=bl.id 1.109 +WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) 1.110 +ORDER by DATE DESC, TITLE, ctime;" 1.111 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\ 1.112 href=\"?replyblog+\1\">VIEW</a></TD>," 1.113 - 1.114 + # echo "$sql2" > tmp/sql.out 1.115 cat<<EOF 1.116 <table class="b searchart"> 1.117 -`sq -header -html $db "$sql"|sed "$sedopt"` 1.118 +`sq -header -html $db "$sql2"|sed "$sedopt"` 1.119 </table> 1.120 EOF 1.121 }