The basic query you suggest works, also the original query works if instead of “select * …” I only do “select id, commercial_name …”.
To me the problem seems to be the size of the fields defined as: sql_file_field.
Below are all the query tests I ran.
-------------------- Query tests -----------------------
Original query:
SELECT * FROM myhb_ru WHERE MATCH (’ (inclisiran|incluiran|incision|indiciran|incluiram) ') AND (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Query:
SELECT * FROM myhb_ru OPTION max_matches=100
Result: Works, 20 rows in set
Query:
SELECT * FROM myhb_ru;
Result: Works, 20 rows in set
Query:
SELECT * FROM myhb_ru where (language_code = 23) OPTION max_matches=100;
Result: Works, 20 rows in set
Query:
SELECT * FROM myhb_ru where (language_code = 23) OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 20 rows in set
Query;
SELECT * FROM myhb_ru where (language_code = 23) LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: works, 100 rows in set
Query:
SELECT * FROM myhb_ru where (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: works, 100 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 2 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|incluiran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 29 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|incluiran|incision’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 100 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|incluiran|incision|indiciran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: ERROR 2000 (HY000): Unknown MySQL error
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|incluiran|incision|incluiram’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 100 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|incluiran|indiciran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
ERROR 2000 (HY000): Unknown MySQL error
Query:
SELECT id, commercial_name FROM myhb_ru where MATCH (‘inclisiran|incluiran|indiciran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 100 rows in set
Query:
SELECT * FROM myhb_ru where MATCH (‘inclisiran|indiciran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
ERROR 2000 (HY000): Unknown MySQL error
Query:
SELECT * FROM myhb_ru where MATCH (‘indiciran’) and (language_code = 23) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
ERROR 2000 (HY000): Unknown MySQL error
Query:
SELECT * FROM myhb_en where MATCH (‘indiciran’) and (language_code = 8) GROUP BY med_id LIMIT 0,100 OPTION max_matches=100, ranker=sph04, field_weights=(commercial_name=2, pharmaceutical_form=2, active_ingredient=2, mah=2, dosage=2, barcode=5);
Result: Works, 12 rows in set
I checked the manual again but I am still confused:
"By default when an index is defined in a configuration file, full-text fields’ original content is both indexed and stored. "
It seems like defining a field as “sql_file_field” makes it a full text field which is by default indexed and stored (can you please confirm ?). The manual entry for sql_file_field: “This directive makes indexer interpret field contents as a file name, and load and index the referred file.” It is not clear from the manual whether this field is stored or not and also if it is stored as a file name or as its content.
From what I see I have been storing Gbytes of files data that I wanted to be indexed but not stored.
Can you please clarify the above ?
What shall I do to specify that I want those files indexed but not stored ?
Thanks
Roberto