Searches in InnoDB using Full Text Search
I am using mysql in InnoDB tables, I am using Match Against query for Full text search.
I am getting below error:
--------------------------
--------------------------
I know InnoDB engine does not support Full text index, so I do not want to change the table engine.
Is there any other alternative?
I am getting below error:
#1214 - The used table type doesn't support FULLTEXT indexes--------------------------
select distinct p.products_image, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name, p.products_ordered ,p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and
((
MATCH(pd.products_name, p.products_model,pd.products_sort_description,m.manufacturers_name) AGAINST ('red')
) ) order by pd.products_name--------------------------
I know InnoDB engine does not support Full text index, so I do not want to change the table engine.
Is there any other alternative?
#1214 - The used table type doesn't support FULLTEXT indexesASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry mate, as far as I know, full-text search is supported only on MyISAM table type.
ASKER
Can we modify query? or some other alternative please.
- In MySQL you can only create a regular index on same table.
- create another MyISAM table to hold the values with Fulltext index and use join.
- Try using 3rd party solutions like sphinx [sphinxsearch.com]
- create another MyISAM table to hold the values with Fulltext index and use join.
- Try using 3rd party solutions like sphinx [sphinxsearch.com]
ASKER
Now I have changed the engine type but still the query is not showing proper record
There is a record related to Big but it is not showing the result.
Is there any character length restriction with full text search?
Please help
There is a record related to Big but it is not showing the result.
Is there any character length restriction with full text search?
Please help
select distinct p.products_image, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name, p.products_ordered ,p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and
((
MATCH(pd.products_name) AGAINST ('Big')
) ) order by pd.products_name
Have you created Fulltext index?
Check your variable value:
SHOW GLOBAL VARIABLES LIKE 'ft_min_word_len'
Refer: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html
SHOW GLOBAL VARIABLES LIKE 'ft_min_word_len'
Refer: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html
ASKER
ft_min_word_len 4
How I can change this variable by using query? or some alternative solution please
How I can change this variable by using query? or some alternative solution please
put this into my.cnf and restart mysqld.
One reason, you may not get results is that if a word is present more than 50%, it will be considered stopword and will not reflect in result. You have to use binary mode in such cases.
See http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html for full details. There is a lot of info in comments also.
See http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html for full details. There is a lot of info in comments also.
"if a word is present more than 50%,"!!! what is stopword_file for then?
The logic is that in a 'real' dataset if a word is appearing more than half the time, its not worth being searched for. The logic is to 'auto' stop them. Words like articles (a,an,the), prepositions etc. You can anyway use boolean mode which gives better control and basic operations support on keywords (+,- etc).
@brain_box: Any documentation reference!!???
ASKER
s asdf
ASKER
I do not want to change the table engine. Please suggest.