PHP Search?

Posted on 2009-04-21
Last Modified: 2012-05-06
Good afternoon experts.

I have been programming in PHP for a couple of years now so I have a pretty basic understanding of the language and how it works. However I have come into a little road block.

Basically, I am dealing with a database that has over 3 million rows in it, there are 4 main columns in the database, they are POST_TITLE, POST_DATE, POST_CONTENT & POST_AUTHOR.

I need to be able to search quickly through this database for key words.

For example if a user wants to search through the post_title column of the database, and display results i.e. if they searched for Bon Jovi and I had 300 rows in that database with Bon Jovi in the title, then It would find them.

I know that I could use something like SELECT * FROM DB WHERE POST_TITLE LIKE '%Bon Jovi%' and be shown some results, but I never really thing the "Like" in MySQL was very acurate, and I have no doubt that that would be slow.

So do I need to create an index of key words in a seperate table for each post or something???


How do I quickly and easily search through a database that size?
Question by:billy_howard
    LVL 39

    Expert Comment

    by:Roger Baklund
    The easiest solution is to use fulltext indexing:
    LVL 39

    Accepted Solution

    If you create your own keywords table, use a normalized approach: the keywords are unique in the keywords table, and you have a keyword_post table linking keywords to each post.

    Note that "Bon Jovi" is two words:
    select kp1.post_id 
      keyword_post kp1,
      keyword_post kp2,
      keyword k1,
      keyword k2
      k1.word = 'Bon' and k2.word = 'Jovi' and 
      k1.word_id = kp1.word_id and 
      k2.word_id = kp2.word_id and
      kp1.post_id = kp2.post_id

    Open in new window

    LVL 49

    Assisted Solution

    If you run of mysql, look into dbsight. (

    It is a java webapp, but can be integrated very easily with your php webapp.

    Configure the indexing process, then use a xml format to wire your php search through dbsight and process the response to your view.
    LVL 1

    Author Closing Comment


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now