Using sphinx search v2.2.9, this query returns a particular record:
((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) )
this query also returns that same record:
(@(issued) 2007)
but this query (which I believe is an implied "AND" combination of the above two queries does not return the record:
(@(issued) 2007) ((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) )
why?
Update 1:
I can reproduce this problem using the mysql command line, below shows running each of the above 3 tests. Note that id: 187 is in both individual result sets but not the combined result set.
$ mysql -h0 -P9306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.2.9-id64-release (rel22-r5006)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT id, weight() FROM `work` WHERE MATCH('(@(issued) 2007)') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
+------+----------+
| id | weight() |
+------+----------+
| 187 | 1604 |
| 200 | 1604 |
| 215 | 1604 |
..i cutoff these results as irrelevant.
+------+----------+
40 rows in set, 1 warning (0.01 sec)
mysql> SELECT id, weight() FROM `work` WHERE MATCH('((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) )') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
+------+----------+
| id | weight() |
+------+----------+
| 187 | 1560 |
| 383 | 1560 |
+------+----------+
2 rows in set, 1 warning (0.01 sec)
mysql> SELECT id, weight() FROM `work` WHERE MATCH('(@(issued) 2007) ((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) )') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
Empty set, 1 warning (0.01 sec)
mysql>
Update 2:
I should also mention that the "work" index is a distributed index between a number of indexes that includes "iresrevi1_core" and "iresrevi2_core". The "issued" field is null in the iresrevi2_core index (not null in the iresrevi1_core index) and the authori field being searched is null in the opposite index iresrevi1_core (not null in the iresrevi2_core index).
I think it might have something to do with this? I can confirm that if i query the 2 indexes directly the iresrevi1_core index will return the issued search data but the iresrevi2_core index will not return the issued search data. Vice versa, the iresrevi2_core will return the author search data but the iresrevi1_core will not.
I have multiple indexes distributed such that I can index all the fields that I want to search using "non infix" methods (forcing on the "infix" fields to be blank in this index) and all the fields that I want to search using "infix" methods are sitting on the other index with all the "non-infix" fields blanked out. The 2 sources/indexes look like this:
source srcresrevi1 : srcresrev
{
sql_query = \
select SQL_NO_CACHE `work`.`ID` AS `ID`, '' as authori \
from work \
WHERE (`work`.`ID` BETWEEN $start AND $end) \
and `work`.`ID` <= (select max_id from sphinx_deltas where id = 1)
sql_joined_field = authsurname from ranged-query; \
SELECT SQL_NO_CACHE wa.work_id AS ID, s.surname \
from `work_authors` wa, \
`author_surnames` s \
where wa.author_surname_id = s.id \
and wa.work_id >= $start and wa.work_id <= $end \
and `wa`.`work_ID` <= (select max_id from sphinx_deltas where id = 1) \
order by wa.work_id ASC; \
select min(work_id), max(work_id) from `work_authors` \
where work_id <= (select max_id from sphinx_deltas where id = 1)
sql_joined_field = author from ranged-query; \
SELECT SQL_NO_CACHE wa.work_id AS ID, CONCAT(f.given,' ',s.surname) \
from `work_authors` wa, `author_surnames` s, `author_fnames` f \
where wa.author_surname_id = s.id \
and wa.author_fname_id = f.id \
and wa.work_id >= $start and wa.work_id <= $end \
and `wa`.`work_ID` <= (select max_id from sphinx_deltas where id = 1) \
order by wa.work_id ASC; \
select min(work_id), max(work_id) from `work_authors` \
where work_id <= (select max_id from sphinx_deltas where id = 1)
sql_joined_field = issued from ranged-query; \
SELECT SQL_NO_CACHE work_id AS ID, `year` \
from issued \
where work_id >= $start and work_id <= $end \
and work_ID <= (select max_id from sphinx_deltas where id = 1) \
order by work_id ASC; \
select min(work_id), max(work_id) from `issued` \
where work_id <= (select max_id from sphinx_deltas where id = 1)
}
source srcresrevi2 : srcresrev
{
sql_query = \
select SQL_NO_CACHE `work`.`ID` AS `ID`, '' as authsurname, '' as author, '' as issued \
from work \
WHERE (`work`.`ID` BETWEEN $start AND $end) \
and `work`.`ID` <= (select max_id from sphinx_deltas where id = 1)
sql_joined_field = authori from ranged-query; \
SELECT SQL_NO_CACHE wa.work_id, CONCAT(f.given,' ',s.surname) \
from `work_authors` wa, `author_surnames` s, `author_fnames` f \
where wa.author_surname_id = s.id \
and wa.author_fname_id = f.id \
and work_id >= $start and work_id <= $end \
and work_ID <= (select max_id from sphinx_deltas where id = 1) \
order by wa.work_id ASC; \
select min(work_id), max(work_id) from `work_authors` \
where work_id <= (select max_id from sphinx_deltas where id = 1)
}
index iresrevi1_core
{
source = srcresrevi1
path = /home/resrev/pubrevit/db/sphinx/development/iresrevi1
docinfo = extern
dict = keywords
mlock = 0
morphology = stem_en
charset_table = 0..9, A..Z->a..z, _, a..z, \
U+410..U+42F->U+430..U+44F, U+430..U+44F
min_word_len = 3
expand_keywords = 0
ngram_len = 1
ngram_chars = U+3000..U+2FA1F
html_strip = 1
html_remove_elements = style, script, head, DOCTYPE, !DOCTYPE
inplace_enable = 1
index_exact_words = 0
index_sp = 0
index_field_lengths = 1
}
index iresrevi2_core
{
source = srcresrevi2
path = /home/resrev/pubrevit/db/sphinx/development/iresrevi2
docinfo = extern
dict = keywords
mlock = 0
morphology = stem_en
charset_table = 0..9, A..Z->a..z, _, a..z, \
U+410..U+42F->U+430..U+44F, U+430..U+44F
min_word_len = 3
min_infix_len = 3
expand_keywords = 1
ngram_len = 1
ngram_chars = U+3000..U+2FA1F
html_strip = 1
html_remove_elements = style, script, head, DOCTYPE, !DOCTYPE
inplace_enable = 1
index_exact_words = 0
index_sp = 1
index_field_lengths = 1
}
So yes, its the multiple distinct indexes that is the problem. UNION not JOIN.
As well as the thread you found, there is a more recent thread here too, which mentions possibly using @@relaxed to work around it. It will probably sill work for a distributed index.