Here is the scenario. I am making a custom blogging software for my site. I am implementing a search feature. It's not very sophisticated - basically it just takes the search phrase entered and runs this query:

$query="SELECT * FROM `blog` WHERE `title` LIKE '%$q%' OR `post` LIKE '%$q%'";

Which is meant to simply search the title and post body for the phrase entered. Is there a better way to do that, keeping in mind how long it would take to run the query on up to 100 rows, each with a post length of up to 1500 characters? I have considered using a LIMIT statement to (sometimes) restrict the number of rows that the query would examine. Good idea?

  • 2
    do some research on Full Text Search and CONTAINS. It searches 10 times faster then a like statement. You do however need to make an index on the table and afaik seaches substrings from the start of words, not in the middle of words.
    – Pieter B
    Jul 4, 2012 at 21:48

2 Answers 2


EHm, I agree with @Mike Brown Answer, With full text search, your blog will be better. Because full text seerch user can type the second word and others.

First yout table must be MyISAM with ALTER TABLE blog ENGINE=MyISAM

and add your table column with full text.

so you will change your query like this

  $query="SELECT * FROM `blog` WHERE MATCH (title,post) AGAINST('+$q' IN BOOLEAN MODE)";

I Hope it Help you. :)


At the risk of sounding like Clippy, it looks like you're trying to enable full text search for your blog. (Or whatever else your example is serving as a proxy for.) I'd say, let the database do what it's optimized for and bring in a dedicated search engine like Lucene.

  • That really seems like overkill when MySQL can do this OK already. Jul 4, 2012 at 22:47
  • @David: what, seriously? MyISAM's fulltext indexing is pretty horrible. I'm hoping that InnoDB's implementation turns out to be reasonable, though blogs.innodb.com/wp/2011/12/…
    – TehShrike
    Jul 5, 2012 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.