I'm writing a plugin that needs to count the number of words in the first post of a thread. What would be better in this case? Selecting the pagetext and then counting in php or just doing everything with one mysql select statement?
OR should I just build something to count the number of words in each thread, store the value in the post table, and just select that when needed?
It depends; how much load do both versions cause and how long do both versions need to finish its execution?
Yeah, that's what I'm thinking about. I have no easy way to measure the load of each and was hoping someone could provide some insight. The query I would be using looks something like this:
Code:
SELECT LENGTH(pagetext) - LENGTH(REPLACE(pagetext, ' ', ''))+1 AS words FROM post WHERE parentid='0' AND threadid=123;
And would potentially run on every thread page load. It seems redundant to run this every time if the count of the words isn't absolutely critical. It would only change if someone edits the post, and that isn't really a big deal.
Maybe I'm answering my own question here and should just store the value. I'm sure a select statement without math is less expensive, especially if you have a really long thread.
Yeah, it may or may not make enough difference to worry about whether to have the db server do the actual counting or to do it in php, but storing it sidesteps the issue and avoids an extra query, so I'd probably do that. And you mentioned above adding it to the post table, but from what you said it sounds like the thread table would be better.
I'm pretty sure it would be faster to do this PHP. However, I think that storing the value would also be a nice alternative.
Quote:
Originally Posted by kh99
Yeah, it may or may not make enough difference to worry about whether to have the db server do the actual counting or to do it in php, but storing it sidesteps the issue and avoids an extra query, so I'd probably do that. And you mentioned above adding it to the post table, but from what you said it sounds like the thread table would be better.
Well, either way you'd have a query since you still have to get the data. Storing in the thread table would require a join that could slow things down on very large forums. The data is available in the post table, so I figured that would be easier. Either way, I'm not seeing any slowness with running the query on the fly. Meh, I don't know, I'll have to see how it works.
Well, either way you'd have a query since you still have to get the data. Storing in the thread table would require a join that could slow things down on very large forums. ...
I was basing that on the idea that if you add a column to the thread table it would get read in with the rest of the row for the current thread, so you wouldn't have to do anything else. But I'm not 100% sure of that, I'd have to check the code.
If I understood you, you want the word count for the first post of a thread even if it's not being displayed on the current page? Then you'd either have to store that same count in each post of the thread or else always read the first post.
07-22-2014, 11:17 AM
Count words in PHP or mysql?
Linear Mode
