[pLog-svn] db optimization

[Jon Daley]

On Sun, 2 Apr 2006, Christoph Feddersen wrote:
> Can you send me the slow query log aswell as the logfile that contains
> all mysql queries?
 	My blog hasn't had any "slow" queries.  Oscar's has had three, 
though the one is probably not really slow, but instead slow based on 
other queries happening at the time.
 	Oscar - can you send your log file, checking for anything that you 
don't want sent?

>> I made a post earlier, and I think there are two SQL queries for each
>> word I typed: 708 SQL queries in 2 seconds.
>
> Well, that should be optimized. Is that filter working with ajax? It
> seems to make a request and database query after typing in a new character?

 	It doesn't dynamically check each word, but when the post is made, 
it adds every word in the post to the bayesian filter, and increments the 
non-spam occurence if the word already exists.  I was wondering if an 
UPDATE followed by an INSERT is better than a SELECT followed by an 
INSERT or UPDATE.  I guess it depends on whether you type the same sorts 
of words repeatedly.  But, the current model always has two queries, where 
my way would sometimes have two and sometimes have one.


Slow query log is attached.
My plog.log is here:
http://jon.limedaley.com/plog.log.gz
  though remember that the slow queries go with Oscar's blog, and not mine.
-------------- next part --------------
use oscar_plog;
UPDATE plog_bayesian_tokens SET spam_occurrences = 0, nonspam_occurrences = 26, prob = 0.01 WHERE id=5764;
# Time: 060401  3:39:34
# User at Host: libertynews[libertynews] @ localhost []
# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 23902


# Time: 060401 10:15:21
# User at Host: oscar[oscar] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 6  Rows_examined: 1864
use oscar_plog;
SELECT a.id as id, a.id, a.date,
                             a.user_id,a.blog_id,a.status,a.properties,
                             a.num_reads, a.slug, 1 AS relevance, a.num_comments AS num_comments,
                                                         a.num_nonspam_comments AS num_nonspam_comments, a.num_trackbacks AS num_trackbacks,
                                                         a.num_nonspam_trackbacks AS num_nonspam_trackbacks, 
                                                         a.global_category_id AS global_category_id,
                                                         a.in_summary_page AS in_summary_page
                                                         FROM plog_articles a, plog_articles_categories c, 
                             plog_article_categories_link l WHERE  a.blog_id = 2 AND c.id = l.category_id AND a.id = l.article_id AND c.in_main_page = 1 AND 
a.status = '1' AND a.date <= '20060401101518' GROUP BY a.id  ORDER BY a.date DESC LIMIT 6;
# Time: 060401 14:39:58
# User at Host: oscar[oscar] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 6  Rows_examined: 1864
SELECT a.id as id, a.id, a.date,
                             a.user_id,a.blog_id,a.status,a.properties,
                             a.num_reads, a.slug, 1 AS relevance, a.num_comments AS num_comments,
                                                         a.num_nonspam_comments AS num_nonspam_comments, a.num_trackbacks AS num_trackbacks,
                                                         a.num_nonspam_trackbacks AS num_nonspam_trackbacks, 
                                                         a.global_category_id AS global_category_id,
                                                         a.in_summary_page AS in_summary_page
                                                         FROM plog_articles a, plog_articles_categories c, 
                             plog_article_categories_link l WHERE  a.blog_id = 2 AND c.id = l.category_id AND a.id = l.article_id AND c.in_main_page = 1 AND 
a.status = '1' AND a.date <= '20060401143955' GROUP BY a.id  ORDER BY a.date DESC LIMIT 6;