[bbDev] Suggestions for search

[fernando]

Couple things:

1. i've been told that the search function returns errors in the last 
few nightly builds.
seen it for myself.

having been asked why mine's working, i could only refer them to the 
dev area of bbpress.org

however, Matt's fixes seem to be gone (as i assumed they were included.)

to see what i mean go search here:
http://blog.saint-elie.com/forum


2. a contribution to my testing forum came in and made sense to me:

"Okay, I see there are a few problems.

A) The database is not properly designed:

SELECT * FROM $bbdb->topics JOIN $bbdb->posts ON topic_last_post_id = 
post_id WHERE LOWER(topic_title) LIKE ('%$likeit%') ORDER BY post_time 
DESC LIMIT 5

Notice that the 'topics' table maintains a 'last post ID'. Why? You can 
determine the "last post" via the post_time. Posts should maintain 
topic_id, not the other way around. Then the topic table will not have 
to be updated every time someone posts a followup.

B) You're only searching the topic title. Not the message body.

C) Once the database is redesigned, you can query for the "last 5" 
appropriate topics using:

SELECT *
  FROM $bbdb->topics AS t
  JOIN $bbdb->posts AS p ON t.topic_id = p.topic_id
  WHERE
  (LOWER(topic_title) LIKE ('%$likeit%')
  OR EXISTS
  (SELECT *
  FROM $bbdb->posts AS p2
  WHERE p2.topic_id = p.topic_id
  AND LOWER(p2.post_text) LIKE ('%$likeit%')))
  AND p.post_time = (SELECT MAX(post_time)
  FROM #bbdb->posts AS p3
  WHERE p3.topic_id = p.topic_id)
  ORDER BY p.post_time DESC LIMIT 5"

My response was:

"One thing i can see is that topic searching is a lot faster, assuming 
that people create appropriate titles.

but i think updating the db is arguably even more time consuming, 
especially if that follow up post isn't going to be included in the 
search.

well spotted!"


Fernando
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: text/enriched
Size: 1862 bytes
Desc: not available
Url : http://one.textdrive.com/pipermail/bbdev/attachments/20050105/d51f2797/attachment.bin