I've built a fulltext index, ft_min_word_len is set to 4, my search returns no results for "christmas" but several results for "Christmas". I couldn't find any documentation on this. Could someone tell me what's going wrong here? I've probably left off a setting variable...
My SQL procedure is:BEGIN IF inAllWords = "on" THEN PREPARE statement FROM "SELECT pk_product, t_name, IF(LENGTH(t_description) <= ?, t_description, CONCAT(LEFT(t_description, ?), '...')) AS description, n_price, n_discounted_price, t_thumbnail FROM product WHERE MATCH (t_name, t_description) AGAINST (? IN BOOLEAN MODE) ORDER BY MATCH (t_name, t_description) AGAINST (? IN BOOLEAN MODE) DESC LIMIT ?, ?"; ELSE PREPARE statement FROM "SELECT pk_product, t_name, IF(LENGTH(t_description) <= ?, t_description, CONCAT(LEFT(t_description, ?), '...')) AS description, n_price, n_discounted_price, t_thumbnail FROM product WHERE MATCH (t_name, t_description) AGAINST (?) ORDER BY MATCH (t_name, t_description) AGAINST (?) DESC LIMIT ?, ?"; END IF; SET @p1 = inShortProductDescriptionLength; SET @p2 = inSearchString; SET @p3 = inStartItem; SET @p4 = inProductsPerPage; EXECUTE statement USING @p1, @p1, @p2, @p2, @p3, @p4;END
By default, the search is performed in case-insensitive fashion. However, you can perform a case-sensitive full-text search by using a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.
Both t_name and t_description are varchar(100), varchar(1000), charset is UTF-8, Collation is utf8_bin. The full text index is compiled against t_name and t_description.
Open in new window