Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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
1  
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
add comment

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

share|improve this answer
add comment

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

Your Answer

 
discard

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.