Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 '12 at 21:48
Thanks @PieterB! Will look in to these. – D. Strout Jul 4 '12 at 21:54

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. :)

share|improve this answer

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.

share|improve this answer
That really seems like overkill when MySQL can do this OK already. – D. Strout Jul 4 '12 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… – TehShrike Jul 5 '12 at 15:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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