full text index

Hi
i have a database table that contains 1 million record and it is growing and growing
and now it is very very slow to see the result via php
my question is it good to create full text index on my table..i m trying but it is not allowing me to create full text index..
or is there another way to speed up the result for a end user
any help please
asaidiAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
I'm guessing the table is using Innodb engine.  It has to be MyISAM for it to support full text indexes.  Now I guess there will be a concern with regard to supporting transactions if you have a mix of InnoDB and MyISAM tables.  What I usually do is to create a MyISAM table that is for the full-text search only, even if it means replicating data across multiple tables.  Then just do a join when you do the search.
0
 
johanntagleCommented:
What error are you encountering when you try to create the index?  Please also share information on your setup - are you on a dedicated server, and do you have MySQL root access?

Using MySQL full-text index will definitely help if you are doing keyword searches.   Just note that there is also a limit to it's scalability.  It's good if MySQL is still able to cache the index in memory, but if not, performance may still be unsatisfactory.  Suggest you also consider 3rd-party engines like Sphinx (see http://sphinxsearch.com/.) - that of course assumes you have control over your servers.
0
 
asaidiAuthor Commented:
error is
1214 the used table does not support full text indexes
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.