0

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

Improve this question
1
  • 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 Commented Jun 15, 2011 at 9:07

1 Answer 1

Reset to default
3

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);
Improve this answer
4
  • Does Wordpress normally create this index on installation? Commented Jun 15, 2011 at 10:18
  • @addedlovely No Commented Jun 15, 2011 at 10:19
  • But I assume it does on ID? Commented Jun 15, 2011 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 . Commented Jun 16, 2011 at 5:22

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.