[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