[bbDev] Suggestions for search
fernando
fernando at dubtribe.com
Wed Jan 5 06:36:06 GMT 2005
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
More information about the bbDev
mailing list