full text index

Posted on 2012-08-22
Medium Priority
Last Modified: 2012-08-24
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
Question by:asaidi
  • 2
LVL 24

Expert Comment

ID: 38323416
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.

Author Comment

ID: 38323429
error is
1214 the used table does not support full text indexes
LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 38323609
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question