Indexing the database?

Who is your host?

Did they say what database table specifically they recommend indexing?

Might look at using phpMyAdmin to optimize your tables especially any that are considered “tables with overhead”.

the host is blue host, and I’m seriously considering moving, after a series of bad experiences.

Currently, they’re hitting me with 3-4 suspensions per day for “high cpu load” or some such, and they’re telling me I should index my WP database.

I am using wp-cache, fyi. I had never heard of anyone indexing a WP database.

And I have optimized the tables through phpmyadmin, too.

Adding some indexes could speed up some things, but not much, I feel.

WordPress does have indexes. Whether they are the right ones or not is anybody’s guess.

I’d like to refresh this topic with three questions:

• Is there a simple tool that I can use to determine what SQL queries are taking up the most CPU %? MySQL database is taking 60-90% CPU resources at peak times. And by simple, I mean something a non-DBA would understand and can use with minimal effort. I have access to myphpadmin, and Putty. I’ve looked at some of the running processes and cannot seem to make sense of it.
• Can it tell me which tables, rows, etc are taking the longest?
• Once I know these things, how can I set up an index or indexes in MySQL phpmyadmin to at least TRY to reduce the query time and CPU load?

Thank you very much for your help.

use the mysql administrator tool to monitor your site. For right now I recommend installing the throttle plugin. It’s in the plugin codex somewhere

I’m just trying to find out which tables can be indexed, and if anyone can help determine the proper way to do it (i.e., which tables to index, and how)

The throttle plugin is ok, the bad thing about it however, is that when it’s using the coral cache site, users cannot comment..

I should add, that myphpadmin mentioned this about some joins.. and that I need indexes somewhere.. I just can’t figure out where (mysql was restarted just 4 hours ago):

Select_full_join 492
The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Here’s an example of a slow query (25 seconds)… it seems as if it’s the “recent comments” plugin, but I cannot be 100% sure.

Can anyone suggest an index (in either wp_posts or wp_comments) that might speed up this query?

I’ve done everything possible under wordpress (caching, sql query cache, opcode cache, and more)..

# Query_time: 25 Lock_time: 0 Rows_sent: 15 Rows_examined: 72625
SELECT wp_posts.ID, post_title, post_name, post_date,
COUNT(wp_comments.comment_post_ID) AS ‘comment_total’ FROM wp_posts LEFT JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_ID WHERE comment_approved = ‘1’ AND post_date_gmt < ‘2007-07-30 13:42:54’ AND post_status = ‘publish’ AND post_password = ” GROUP BY wp_comments.comment_post_ID ORDER BY comment_total DESC LIMIT 15;

Thanks again in advance.

Just a guess. You might be reading ALL posts in order to match the Count condition and the left join condition. Just a guess on my part, I’m no SQL expert.

I do know No amount of indexes will fix a design that won’t scale.

Maybe you can dump that plugin and go up a level and think about what you want, what you’re trying to get done and perhaps there’s a less resource consumptive way to do that.

Just a guess.

I’ve noticed recently using the various MySQL command line tools that many WordPress related queries are *very* inefficient because many of them are doing full table scans.

Most of the time full table scans can be avoided by using indexes on the fields that are part of the “WHERE” portion of an SQL statement.

With that being said, I must say the biggest problem is probably all the plugins written by people who don’t understand how to properly create database schemas that are optimized for the type of queries that will be made on the tables.

I’d love to see some more effort being put into making sure all plugins and even base WordPress SQL tables use indexes properly so that the number of full table scans are reduced.

SouthBeach030: don’t know if you resolved this, but looking at the query the most likely things to check for indexes are the join key which would be wp_posts.ID and wp_comments.comment_post_ID (if the comments table is not indexed here then there’s a scan of the table for each row in the posts table).

The other possibility is the comments table where it’s doing a count of comments for each post in the post table. Put one on post_date_gmt to limit the number of rows, possibly make it a two column index with comment_approved if you moderate comments.