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:
#1214 - The used table type doesn't support FULLTEXT indexes

Open in new window


------------------------------ Query Start-----------------
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

Open in new window


------------------------------ Query End-----------------

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 indexes

Open in new window

GurbirsAsked:
Who is Participating?
 
jkofteConnect With a Mentor Commented:
Fulltext searches are supported by MYISAM tables.

try this:

ALTER TABLE my_table ENGINE = MYISAM;
0
 
GurbirsAuthor Commented:
Is this possible with InnoDB Engine or some other alternative or some other query?
I do not want to change the table engine. Please suggest.
0
 
jkofteCommented:
Sorry mate, as far as I know, full-text search is supported only on MyISAM table type.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GurbirsAuthor Commented:
Can we modify query? or some other alternative please.
0
 
theGhost_k8Database ConsultantCommented:
-  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]
0
 
GurbirsAuthor Commented:
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

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

Open in new window

0
 
theGhost_k8Database ConsultantCommented:
Have you created Fulltext index?
0
 
theGhost_k8Database ConsultantCommented:
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
0
 
GurbirsAuthor Commented:
ft_min_word_len       4
How I can change this variable by using query? or some alternative solution please
0
 
theGhost_k8Database ConsultantCommented:
put this into my.cnf and restart mysqld.
0
 
brain_boxCommented:
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.
0
 
theGhost_k8Database ConsultantCommented:
"if a word is present more than 50%,"!!! what is stopword_file for then?
0
 
brain_boxCommented:
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).
0
 
theGhost_k8Database ConsultantCommented:
@brain_box: Any documentation reference!!???
0
 
GurbirsAuthor Commented:
s asdf
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.