Weighting mysql full-text search results by date

Question

I'm using innodb full-text search to return a set of results where the newest posts are more relevant. Just wanted to check that this the best method:

SELECT *, (MATCH(note) AGAINST ('$search' IN BOOLEAN MODE))/SQRT(GREATEST(1, DATEDIFF(NOW(), date))) AS score FROM notes WHERE userid = '$userid' HAVING score > 0 ORDER BY score DESC

Answer 1

A few "small" comments.

1. Don't use SELECT * for reasons outlined in this question's answers:

   What is the reason to not use select *?

2. Linebreaks and indentation make your code much easier to read:

   SELECT *, (MATCH(note) AGAINST (
       '$search' IN BOOLEAN MODE))/SQRT(GREATEST(1, DATEDIFF(NOW(), date)
       )) AS score 
   FROM notes 
   WHERE userid = '$userid' 
   HAVING score > 0 
   ORDER BY score DESC
   

Things I like:

• You're not writing it ALL IN SHOUT-CASE ;)