1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
#if you have many posts, it's best to configure another index for new posts and
#periodically merge the diff index to the main
#this is not important until you get to hundreds of thousands posts
source src_cnprog
{
# data source
type = mysql
sql_host = localhost
sql_user = cnprog #replace with your db username
sql_pass = secret #replace with your db password
sql_db = cnprog #replace with your db name
# these two are optional
#sql_port = 3306
#sql_sock = /var/lib/mysql/mysql.sock
# pre-query, executed before the main fetch query
sql_query_pre = SET NAMES utf8
# main document fetch query - change the table names if you are using a prefix
# this query creates a flat document from each question that includes only latest
# revisions of the question and all of it's answers
sql_query = SELECT q.id as id, q.title AS title, q.tagnames as tags, qr.text AS text, answers_combined.text AS answers \
FROM question AS q \
INNER JOIN \
( \
SELECT MAX(id) as id, question_id \
FROM question_revision \
GROUP BY question_id \
) \
AS mqr \
ON q.id=mqr.question_id \
INNER JOIN question_revision AS qr ON qr.id=mqr.id \
LEFT JOIN \
( \
SELECT GROUP_CONCAT(answer_current.text SEPARATOR '. ') AS text, \
question_id \
FROM \
( \
SELECT a.question_id as question_id, ar.text as text \
FROM answer AS a \
INNER JOIN \
( \
SELECT MAX(id) as id, answer_id \
FROM answer_revision \
GROUP BY answer_id \
) \
AS mar \
ON mar.answer_id = a.id \
INNER JOIN answer_revision AS ar ON ar.id=mar.id \
WHERE a.deleted=0 \
) \
AS answer_current \
GROUP BY question_id \
) \
AS answers_combined ON q.id=answers_combined.question_id \
WHERE q.deleted=0;
# optional - used by command-line search utility to display document information
sql_query_info = SELECT title, id FROM question WHERE id=$id
}
index cnprog {
# which document source to index
source = src_cnprog
# this is path and index file name without extension
# you may need to change this path or create this folder
path = /var/data/sphinx/cnprog_main
# docinfo (ie. per-document attribute values) storage strategy
docinfo = extern
# morphology
morphology = stem_en
# stopwords file
#stopwords = /var/data/sphinx/stopwords.txt
# minimum word length
min_word_len = 1
# uncomment next 2 lines to allow wildcard (*) searches
#min_infix_len = 1
#enable_star = 1
# charset encoding type
charset_type = utf-8
}
# indexer settings
indexer
{
# memory limit (default is 32M)
mem_limit = 64M
}
# searchd settings
searchd
{
# IP address on which search daemon will bind and accept
# optional, default is to listen on all addresses,
# ie. address = 0.0.0.0
address = 127.0.0.1
# port on which search daemon will listen
port = 3312
# searchd run info is logged here - create or change the folder
log = /var/log/sphinx/searchd.log
# all the search queries are logged here
query_log = /var/log/sphinx/query.log
# client read timeout, seconds
read_timeout = 5
# maximum amount of children to fork
max_children = 30
# a file which will contain searchd process ID
pid_file = /var/log/sphinx/searchd.pid
# maximum amount of matches this daemon would ever retrieve
# from each index and serve to client
max_matches = 1000
}
|
