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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkofteCommented:
Fulltext searches are supported by MYISAM tables.

try this:

ALTER TABLE my_table ENGINE = MYISAM;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.