On Tue, Dec 10, 2002 at 04:07:04PM -0800, Brion Vibber wrote:
Is retrieving, transferring, and separately counting the length of the text of potentially hundreds or thousands of linked articles *really* more efficient than a second query for LENGTH(text), a value which the database should already know and thus does not have to spend time zipping through strings looking for null bytes?
In fact, I was assuming the opposite; that doing a second query to find the size and existance of all the links would be much more efficient.
I also think we see significant savings (if not now, in the future when there are more pageviews than edits) of CPU by storing the article size, instead of calculating it every time we access it.
How is searching currently done?
http://www.mysql.com/doc/en/Fulltext_Search.html
For multi-word searches we AND and OR multiple MATCH/AGAINSTs together in one query, which may not the best way to do it. MySQL 4.0 has boolean features built right in, but we're using the more stable 3.x.
Thank you for pointing that out, Brion. I did some more research; PostgreSQL has a contributed fulltext searching module which we can enable to get the same functionality. It will definately be preferable to using a LIKE clause.
Jonathan