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 '12 at 21:48

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

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

| 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 blogs.innodb.com/wp/2011/12/… – TehShrike Jul 5 '12 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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