full text index

Posted on 2012-08-22
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
    LVL 24

    Expert Comment

    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 - that of course assumes you have control over your servers.

    Author Comment

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

    Accepted Solution

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Read about achieving the basic levels of HRIS security in the workplace.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now