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
|
create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*";
select * from ts where q='test';
id w q
1 2 test
2 2 test
4 1 test
5 1 test
drop table ts;
create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*";
select * from ts where q='test;filter=gid,1;mode=extended';
id w q
1 2379 test;filter=gid,1;mode=extended
2 2379 test;filter=gid,1;mode=extended
5 1412 test;filter=gid,1;mode=extended
select * from ts where q='test|one;mode=extended';
id w q
1 3579 test|one;mode=extended
2 2439 test|one;mode=extended
4 1456 test|one;mode=extended
5 1456 test|one;mode=extended
select * from ts where q='test;offset=1;limit=1';
id w q
2 2 test;offset=1;limit=1
alter table ts connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/test1";
select id, w from ts where q='one';
id w
1 2
drop table ts;
create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, gid int not null, _sph_count int not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/test1";
select * from ts;
id w q gid _sph_count
select * from ts where q='';
id w q gid _sph_count
1 1 1 0
2 1 1 0
3 1 2 0
4 1 2 0
5 1 1 0
select * from ts where q=';groupby=attr:gid';
id w q gid _sph_count
3 1 ;groupby=attr:gid 2 2
1 1 ;groupby=attr:gid 1 3
explain select * from ts where q=';groupby=attr:gid';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ts ref q q 257 const 3 Using where with pushed condition
SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='index_condition_pushdown=off';
explain select * from ts where q=';groupby=attr:gid';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ts ref q q 257 const 3 Using where with pushed condition
SET optimizer_switch=@save_optimizer_switch;
drop table ts;
show status like "sphinx_total%";
Variable_name Value
Sphinx_total 2
Sphinx_total_found 2
show status like "sphinx_word%";
Variable_name Value
Sphinx_word_count 0
Sphinx_words
create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*";
select * from ts where q=';filter=meta.foo_count,100';
id w q
1 1 ;filter=meta.foo_count,100
select * from ts where q='test;filter=meta.sub.int,7';
id w q
5 1 test;filter=meta.sub.int,7
select * from ts where q=';filter=meta.sub.list[0],4';
id w q
select * from ts where q=';filter=meta.sub.list[1],4';
id w q
5 1 ;filter=meta.sub.list[1],4
select * from ts where q='test;range=meta.foo_count,100,500';
id w q
1 2 test;range=meta.foo_count,100,500
5 1 test;range=meta.foo_count,100,500
drop table ts;
#
# MDEV-19205: Sphinx unable to connect using a host name
#
create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://localhost:SPHINXSEARCH_PORT/*";
select * from ts where q=';filter=meta.foo_count,100';
id w q
1 1 ;filter=meta.foo_count,100
select * from ts where q='test;filter=meta.sub.int,7';
id w q
5 1 test;filter=meta.sub.int,7
select * from ts where q=';filter=meta.sub.list[0],4';
id w q
select * from ts where q=';filter=meta.sub.list[1],4';
id w q
5 1 ;filter=meta.sub.list[1],4
select * from ts where q='test;range=meta.foo_count,100,500';
id w q
1 2 test;range=meta.foo_count,100,500
5 1 test;range=meta.foo_count,100,500
drop table ts;
|
