Give weights to matching fields

Question

Have a problem with Sphinxql. I try to match several fields and give each a weight.

select id
       ,item
        ,param1
        ,param2
        ,module_id
        ,param2_id
        ,date_change
        ,custom_rank
        ,weight() as rank
        ,in(site, 336935152)
                        and if(date_from, date_from, 1578400079) <= 1578400079
                        and if(date_to, date_to, 1578400079) >= 1578400079
                        and ((((module_id = 3674251022)and(param1_id = 455881287)and(param2_id = 4196041389)))) as cond1
        ,if(date_to, date_to, 1578400079) date_to_nvl
        ,if(date_from, date_from, 1578400079) date_from_nvl
        from index
        where MATCH('@(title,body) (search query)')
        limit 0, 500
        option max_matches = 500
        ,field_weights=(title=99999, body=1)

But weight doesn't matter. Even if I give 99999 weight to title and 1 to body and vice versa, query result doesn't change. My goal is to make sphinx, firstly match title and after body.

UPD: packedfactors() query result

+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id   | weight() | packedfactors()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1055 |  1000631 | bm25=631, bm25a=0.650515, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=5, word_count=1, tf_idf=0.790203, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=3, min_best_span_pos=3, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=6, idf=0.158041)   |
| 1056 |  1000631 | bm25=631, bm25a=0.650515, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=5, word_count=1, tf_idf=0.790203, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=3, min_best_span_pos=3, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=6, idf=0.158041)   |
|  242 |  1000627 | bm25=627, bm25a=0.649095, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.632162, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=5, idf=0.158041)   |
|  813 |  1000627 | bm25=627, bm25a=0.649095, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.632162, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=5, idf=0.158041)   |
|  815 |  1000627 | bm25=627, bm25a=0.649095, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.632162, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=5, idf=0.158041)   |
| 1054 |  1000627 | bm25=627, bm25a=0.649095, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.632162, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=5, idf=0.158041)   |
|  334 |  1000621 | bm25=621, bm25a=0.647014, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.474122, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=4, idf=0.158041)   |
|  335 |  1000621 | bm25=621, bm25a=0.647014, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.474122, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=4, idf=0.158041)   |
|  510 |  1000621 | bm25=621, bm25a=0.647014, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=8, min_best_span_pos=8, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.474122, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=10, min_best_span_pos=10, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=4, idf=0.158041) |
| 1057 |  1000621 | bm25=621, bm25a=0.647014, field_mask=3, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.158041, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.474122, min_idf=0.158041, max_idf=0.158041, sum_idf=0.158041, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.158041, atc=0.000000), word0=(tf=4, idf=0.158041)   |
+------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

Use PACKEDFACTORS() to understand what's going on, there may be different reasons why your ranking doesn't change.

Below you can see an example which shows how to use it and that for example for some documents stats are only exposed for one field of 2, because the keywords were found only in one field.

MySQL [(none)]> select id, weight(), packedfactors() from index where match('@(subject,body)manticore search') limit 5 option ranker=expr('sum(lcs*user_weight)*1000+bm25'), field_weights=(subject=1,body=10)\G
*************************** 1. row ***************************
             id: 67020137501
       weight(): 20614
packedfactors(): bm25=614, bm25a=0.67905319, field_mask=2, doc_word_count=2, field1=(lcs=2, hit_count=3, word_count=2, tf_idf=0.30208117, min_idf=0.07970884, max_idf=0.14266349, sum_idf=0.22237234, min_hit_pos=5, min_best_span_pos=17, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=2, wlccs=0.22237234, atc=0.02281057), word0=(tf=1, idf=0.14266349), word1=(tf=2, idf=0.07970884)
*************************** 2. row ***************************
             id: 67020139037
       weight(): 20614
packedfactors(): bm25=614, bm25a=0.67905319, field_mask=2, doc_word_count=2, field1=(lcs=2, hit_count=3, word_count=2, tf_idf=0.30208117, min_idf=0.07970884, max_idf=0.14266349, sum_idf=0.22237234, min_hit_pos=74, min_best_span_pos=86, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=2, wlccs=0.22237234, atc=0.02281057), word0=(tf=1, idf=0.14266349), word1=(tf=2, idf=0.07970884)
*************************** 3. row ***************************
             id: 67164506141
       weight(): 20601
packedfactors(): bm25=601, bm25a=0.67105567, field_mask=2, doc_word_count=2, field1=(lcs=2, hit_count=2, word_count=2, tf_idf=0.22237234, min_idf=0.07970884, max_idf=0.14266349, sum_idf=0.22237234, min_hit_pos=105, min_best_span_pos=105, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=2, wlccs=0.22237234, atc=0.02248834), word0=(tf=1, idf=0.14266349), word1=(tf=1, idf=0.07970884)
*************************** 4. row ***************************
             id: 60360225821
       weight(): 11653
packedfactors(): bm25=653, bm25a=0.70337892, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08168077, min_idf=0.08168077, max_idf=0.08168077, sum_idf=0.08168077, min_hit_pos=3, min_best_span_pos=3, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08168077, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.60435468, min_idf=0.15108867, max_idf=0.15108867, sum_idf=0.15108867, min_hit_pos=61, min_best_span_pos=61, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15108867, atc=0.00027331), word0=(tf=4, idf=0.15108867), word1=(tf=1, idf=0.08168077)
*************************** 5. row ***************************
             id: 59004972573
       weight(): 11645
packedfactors(): bm25=645, bm25a=0.70018470, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08168077, min_idf=0.08168077, max_idf=0.08168077, sum_idf=0.08168077, min_hit_pos=11, min_best_span_pos=11, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08168077, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.45326602, min_idf=0.15108867, max_idf=0.15108867, sum_idf=0.15108867, min_hit_pos=56, min_best_span_pos=56, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15108867, atc=0.00005638), word0=(tf=3, idf=0.15108867), word1=(tf=1, idf=0.08168077)
5 rows in set (0.01 sec)

MySQL [(none)]> select id, weight(), packedfactors() from index where match('@(subject,body)manticore search') limit 5 option ranker=expr('sum(lcs*user_weight)*1000+bm25'), field_weights=(subject=10,body=1)\G
*************************** 1. row ***************************
             id: 60360225821
       weight(): 11653
packedfactors(): bm25=653, bm25a=0.70337892, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08168077, min_idf=0.08168077, max_idf=0.08168077, sum_idf=0.08168077, min_hit_pos=3, min_best_span_pos=3, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08168077, atc=0.000000), field1=(lcs=1, hit_count=4, word_count=1, tf_idf=0.60435468, min_idf=0.15108867, max_idf=0.15108867, sum_idf=0.15108867, min_hit_pos=61, min_best_span_pos=61, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15108867, atc=0.00027331), word0=(tf=4, idf=0.15108867), word1=(tf=1, idf=0.08168077)
*************************** 2. row ***************************
             id: 59004972573
       weight(): 11645
packedfactors(): bm25=645, bm25a=0.70018470, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08168077, min_idf=0.08168077, max_idf=0.08168077, sum_idf=0.08168077, min_hit_pos=11, min_best_span_pos=11, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08168077, atc=0.000000), field1=(lcs=1, hit_count=3, word_count=1, tf_idf=0.45326602, min_idf=0.15108867, max_idf=0.15108867, sum_idf=0.15108867, min_hit_pos=56, min_best_span_pos=56, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15108867, atc=0.00005638), word0=(tf=3, idf=0.15108867), word1=(tf=1, idf=0.08168077)
*************************** 3. row ***************************
             id: 52749413289
       weight(): 11631
packedfactors(): bm25=631, bm25a=0.69566667, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.14696111, min_idf=0.14696111, max_idf=0.14696111, sum_idf=0.14696111, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.14696111, atc=0.000000), field1=(lcs=1, hit_count=2, word_count=2, tf_idf=0.23519781, min_idf=0.08823670, max_idf=0.14696111, sum_idf=0.23519781, min_hit_pos=23, min_best_span_pos=23, exact_hit=0, max_window_hits=1, min_gaps=2, exact_order=0, lccs=1, wlccs=0.14696111, atc=0.00378523), word0=(tf=2, idf=0.14696111), word1=(tf=1, idf=0.08823670)
*************************** 4. row ***************************
             id: 69779455599
       weight(): 11609
packedfactors(): bm25=609, bm25a=0.68568671, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.15835057, min_idf=0.15835057, max_idf=0.15835057, sum_idf=0.15835057, min_hit_pos=11, min_best_span_pos=11, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15835057, atc=0.000000), field1=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08304220, min_idf=0.08304220, max_idf=0.08304220, sum_idf=0.08304220, min_hit_pos=32, min_best_span_pos=32, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08304220, atc=0.000000), word0=(tf=1, idf=0.15835057), word1=(tf=1, idf=0.08304220)
*************************** 5. row ***************************
             id: 53174602295
       weight(): 11605
packedfactors(): bm25=605, bm25a=0.67905343, field_mask=3, doc_word_count=2, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.08168077, min_idf=0.08168077, max_idf=0.08168077, sum_idf=0.08168077, min_hit_pos=9, min_best_span_pos=9, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.08168077, atc=0.000000), field1=(lcs=1, hit_count=1, word_count=1, tf_idf=0.15108867, min_idf=0.15108867, max_idf=0.15108867, sum_idf=0.15108867, min_hit_pos=35, min_best_span_pos=35, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=0, lccs=1, wlccs=0.15108867, atc=0.000000), word0=(tf=1, idf=0.15108867), word1=(tf=1, idf=0.08168077)
5 rows in set (0.01 sec)