Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am using a WordPress plugin called feedwordpress in order to run a planet like website on wordpress (See it here).

The plugin is great except for one thing - it hogs down my (VPS) server into submission once every week or so.

In a recent e-mail exchange with the webadmin he wrote the following:

It does look like the increased mysql resource usage is being caused by slow queries being run by r-bloggers.com. Here is a copy of some of the logs that are being produced. You would need to optimize this site and database further to have it running as efficiently as possible. If these changes have already been made, your best option would be to look into a large upgrade for your VPS due to the high level or resources and traffic that your site needs and sees.

Here are the logs:

# Time: 110614 16:11:35
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 104 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 1570, 10;
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 237 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:18:13
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 257 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (MD5(guid) = '956e208f101562f6654e88e9711276e4')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:19:02
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 83 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (MD5(guid) = '6c589e661f03a67b0529fab2f080bfd3')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 1440, 10;

Which leads me to my question - what in this logs might indicate to me what is happening (why should such queries take so long?)? is it possible to optimize these? If so, how?

Thanks, Tal

share|improve this question
2  
I think this question doesn't belong here. You're asking for help on optimizing SQL queries. I think this would be better on dba.stackexchange.com or stackoverflow.com –  Brady Jun 15 '11 at 9:07

1 Answer 1

Your log shows that MySQL is performing a linear scan of all table records to get the necessary row - this is because there is no index for guid

Assuming wp_rb_posts is just the normal posts table with a slightly modified prefix, then you can just add an index for that field, and you'll see a dramatic improvement.

The SQL statement for doing this is:

CREATE INDEX guid ON wp_rb_posts(guid);
share|improve this answer
    
Does Wordpress normally create this index on installation? –  addedlovely Jun 15 '11 at 10:18
    
@addedlovely No –  anu Jun 15 '11 at 10:19
    
But I assume it does on ID? –  addedlovely Jun 15 '11 at 10:24
    
Thanks Spartacus. I ran the command "SHOW INDEX FROM wp_rb_posts" in phpMyAdmin and it shows (I think) that such an index exists. Here is the row output (what do you think?): wp_rb_posts 1 wp_rb_posts_guid_idx 1 guid A 54624 NULL NULL BTREE . –  Tal Galili Jun 16 '11 at 5:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.