Hi Matthew Paulson, I see your using W3 Total Cache but your database and object cache is set to disk. Caching objects and your database to disk can actually have a negative performance effect especially if your getting that much traffic. You can read more about the effects on caching database and objects to disk … Read more
While this is really a MySQL question it does help to understand the WordPress SQL schema and also I love trying to optimize SQL queries so rather than send you off to StackOverflow I’ll try to answer you here. You may still want to post it over there to get some other opinions. And while … Read more
This is what I’ve found. I’m using a filter to filter the ORDER BY on the SQL query generated by WP_Query. The filter is this posts_orderby. And with it, you can write a custom ORDER BY for the query. I’m gonna show you an example. add_filter(‘posts_orderby’, ‘posts_orderby’); function posts_orderby($orderby_for_query) { $orderby_for_query = “LEFT(wp_posts.post_date, 10) DESC, … Read more
Add flush_rewrite_rules(); to your theme’s functions.php, load the page, and them remove it. It is very inefficient to have that run on every page load. Hopefully your edits to the options table are sane. If not, that will not work. Caveat: I think that functions.php is adequate. You may need to add it to a … Read more
In the question you linked to, the solution was to use mysqli_multi_query as the API for executing the SQL. $wpdb desen’t have an API to do a multi query therefor you can’t use it directly, but you can get the handle to the mysql interface from $wpdb->dbh and do something like $sql_trigger = “….”; mysqli_multi_query($wpdb->dbh,$sql_trigger);
I’ve read that dumping the database as text and searching in it is a good way to go. You can search with phpmyadmin, but it’s limited. Depends on the size of the database and a good text editor, but you can delete post/page revisions before dumping the database to bring it down in size. Or … Read more
You didn’t mention a HyperDB revision so I’m assuming trunk @337290. HyperDB’s SRTM feature (send reads to master) works two ways. First, it keeps track of which tables have received writes during the current script and sends all subsequent reads for those tables to the master. Second, it gives you a way to force all … Read more
You should be using the wpdb class for all your own queries. All core queries also use wpdb. See wpdb Show and Hide SQL Errors <?php $wpdb->show_errors(); ?> <?php $wpdb->hide_errors(); ?> You can also print the error (if any) generated by the most recent query with print_error. <?php $wpdb->print_error(); ?> Also see SAVEQUERIES constant for … Read more
I had the exact same issue. The problem is not one that can be fixed without modifying some code that you probably shouldn’t (or perhaps writing a filter or a ‘drop-in’). The issue is the CAST directive in the SQL statement. It CASTS the entire table before it does anything, with the amount of records … Read more
Try using this plugin http://wordpress.org/extend/plugins/debug-queries/ for checking performance of your database queries. It shows lots of detatils about each and every query made and time the query needed to complete and the time needed to make the whole page.