Mercurial > hgrepos > hgweb.cgi > 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 wrap: on
line diff
--- a/s4-blog.sh Sat Oct 22 00:39:53 2016 +0859 +++ b/s4-blog.sh Sat Oct 22 22:58:10 2016 +0859 @@ -462,24 +462,25 @@ searchart() { kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC kwdgrp="" + authcond="" if [ -z "$kwd" ]; then echo "検索語を指定してください" | html p; return fi if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then # Like '#1234', assume as artID rowid=$((${kwd#\#} + 0)) # Force to be a number - kc="a.rowid = $rowid" + kc="ar.rowid = $rowid" else for k in `echo "$kwd" | sed "s/'/''/g"`; do if expr x"$k" : 'x@[1-9][][0-9]*-[][0-9:-]*$' >/dev/null 1>&2; then # Like '@2016-10-10', expands to "s.ctime GLOB '@2016-10-10'" ctime=${k#@} # Not sure GROUP BY a.blogid is comfortable for searchers...? - kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'" + kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'" ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then ctime=`date +%F` - kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'" + kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'" else kc=$kc${kc:+" AND "}"content LIKE '%$k%'" fi @@ -499,7 +500,8 @@ fi elif { author=`getpar author`; test -n "$author"; }; then atptn=`sqlquotestr $author` - kc="$kc${kc:+ AND }author=$atptn" + #kc="$kc${kc:+ AND }author=$atptn" + authcond="WHERE author=$atptn" if isuser $author; then echo "(`linkhome $author` さんの書き込みからの検索)" | html p fi @@ -509,41 +511,48 @@ # blog: id=blog-id, author=LeaderAuthor # blog_s: id=blog-id, key='title', val='BLOG-TITLE' # WANT: blog-ROWid,article-id,val(TEXT) - sql="`sql4readableblogs` -- Extract user-readable blogs - SELECT b.rid||'#'||x.id as '', - b.title as TITLE, - substr(x.ctime, 0, 11) as DATE, - substr(x.content, 0, 78) as TEXT - FROM (SELECT blg.rid,blg.*,bs.val as title - FROM readableblogs blg JOIN blog_s bs - ON blg.id=bs.id AND bs.key='title') b - JOIN - (SELECT a.id, - a.blogid, - s.ctime, - s.text /* || ' ' || m.filenames */ content - FROM article a - JOIN - (SELECT id, /* Create [id, ctime, text] from article_s */ - max(CASE key WHEN 'ctime' THEN val END) ctime, - max(CASE key WHEN 'text' THEN val END) text - FROM article_s GROUP BY id) s - /* - JOIN - (SELECT id, group_concat(val) filenames - FROM article_m - WHERE type LIKE 'file:%' - GROUP BY id) m */ - ON a.id=s.id /* AND a.id=m.id */ WHERE $kc$kwdgrp) x - ON b.id=x.blogid - WHERE b.id IN (SELECT id FROM blog_s $cond) - ORDER by DATE DESC, TITLE, x.ctime;" + sql2="`sql4readableblogs` -- Extract user-readable blogs +-- 0.3sec +WITH artsm AS ( + SELECT a.id,ctime, text || ' ' || coalesce(files, '') content + FROM article a + LEFT JOIN + (SELECT ars.id, ctime, text, coalesce(files, '') files + FROM (SELECT id, + max(CASE key WHEN 'ctime' THEN val END) ctime, + max(CASE key WHEN 'text' THEN val END) text + FROM article_s + GROUP BY id) ars + LEFT JOIN + (SELECT id, group_concat(val) files + FROM article_m + WHERE type LIKE 'file:%' + GROUP BY id) arm + ON ars.id=arm.id + ) ar + ON a.id=ar.id +), ar AS ( + SELECT article.rowid, article.blogid, article.id, ctime, content + FROM article JOIN artsm ON article.id=artsm.id + $authcond +), bl AS ( + SELECT blg.rid, blg.*, blog_s.val TITLE + FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title' +) +SELECT bl.rid||'#'||ar.id '', + bl.title TITLE, + substr(ctime, 0, 11) DATE, + substr(content, 0, 78) TEXT +FROM ar JOIN bl + ON ar.blogid=bl.id +WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) +ORDER by DATE DESC, TITLE, ctime;" sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\ href=\"?replyblog+\1\">VIEW</a></TD>," - + # echo "$sql2" > tmp/sql.out cat<<EOF <table class="b searchart"> -`sq -header -html $db "$sql"|sed "$sedopt"` +`sq -header -html $db "$sql2"|sed "$sedopt"` </table> EOF }
