[pLog-svn] db optimization
Jon Daley
plogworld at jon.limedaley.com
Fri Mar 31 21:14:58 GMT 2006
I turned on slow query logging, and no-index queries.
So, far, I have only gotten one:
UPDATE plog_bayesian_tokens SET spam_occurrences = 0, nonspam_occurrences
= 26, prob = 0.01 WHERE id=5764;
The context has tons of these: SELECT/UPDATE pairs.
Is there a way to fix each pair to just be one query, ie something like:
"UPDATE plog_bayesian_tokens SET spam_occurrences = spam_occurences+1
WHERE token = 'Url#html' AND blog_id=2"
I don't know how the probability tag works, so maybe that isn't possible.
I also wonder if it is more efficient to have one query with lots of
'WHERE token = "asdasd" OR token = "qweqwe"' etc.
It probably also needs to be able to INSERT rows if they didn't already
exist...
I made a post earlier, and I think there are two SQL queries for each word
I typed: 708 SQL queries in 2 seconds.
Here is an excerpt from a spammer on Oscar's blog:
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: SELECT * FROM plog_bayesian_tokens WHERE token = 'Url#html' AND
blog_id = '2'
31-03-2006 14:42:24 DEBUG - [cache.class.php:87 (logger:debug)] Removing
from cache 360 (article_text)
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: UPDATE plog_bayesian_tokens SET spam_occurrences = 607,
nonspam_occurrences = 7507, prob
= 0.01 WHERE id=635;
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: SELECT * FROM plog_bayesian_tokens WHERE token = 'Url#http' AND
blog_id = '2'
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: UPDATE plog_bayesian_tokens SET spam_occurrences = 3904,
nonspam_occurrences = 9901, pro
b = 0.01 WHERE id=630;
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: SELECT * FROM plog_bayesian_tokens WHERE token =
'Url#omurtravel' AND blog_id = '2'
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: UPDATE plog_bayesian_tokens SET spam_occurrences = 0,
nonspam_occurrences = 24, prob = 0
.01 WHERE id=7353;
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: SELECT * FROM plog_bayesian_tokens WHERE token =
'Url#zverie-moe' AND blog_id = '2'
31-03-2006 14:42:24 DEBUG - [model.class.php:124 (logger:debug)] Executing
SQL Query: UPDATE plog_bayesian_tokens SET spam_occurrences = 0,
nonspam_occurrences = 388, prob =
0.01 WHERE id=5562;
**************************************
Jon Daley
http://jon.limedaley.com/
Legalism is adding stuff to the cross.
-- C.J. Mahaney
More information about the pLog-svn
mailing list