After trying various things to try to speed up my WordPress install, I finally found out that it was View's MySQL queries that were slowing down my site dramatically.
<b>Views is simply making too many queries to the database, and it's not necessary.</b>
Photo - My table View that is just a sortable 7 column table:
hidden link
Right now the page is showing 13 results (rows) in the table. Take a guess at how many total queries it takes to display this page? This is how many times it has to access and query the database. 10? Nope. 30? Even this would be high, but no. 50? Keep going. 80? Close. <b>It's over a 100 times.</b>
Photo - The total query results display from WordPress's built in Save Queries functionality:
hidden link
WordPress article on Saving Queries for analysis:
http://codex.wordpress.org/Editing_wp-config.php#Save_queries_for_analysis
That's right. For a table with only 13 rows, WordPress is making 101 queries. The "Total Time" is just execution time, and does not include the roundtrip "waiting" latency that is the real performance killer as you can see here in network tab of the Chrome Developer Tools:
Photo - Screenshot of Chrome Developer Tools - Network Timeline:
hidden link
It takes on average 4-5 seconds just to receive the pages HTML (and I get spikes of 10-20 seconds when the server is under load). This is due to the over 100 separate times it queries the database.
What is happening is Views is making a separate query for EACH and EVERY field that is being displayed in the View. So if you have 7 columns, 1 row will equal 7 queries. 2 rows = 14, 3 rows = 21...etc.
Here is the queries just for a single row:
SELECT post_id, meta_key, meta_value FROM cms_postmeta WHERE post_id IN (659,657,640,637,632,629,618,615,613,609,607,603)
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-rep-name'
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-estimate-type'
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-estimate-status'
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-client-name'
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-client-address'
SELECT * FROM cms_postmeta WHERE post_id=603 AND meta_key='wpcf-final-total-numeric'
You should never have to query the same table more than once per request. What should be happening is a more generalized query that results in a larger array, and let PHP's MySQL functions to sort and filter those results (fetch_assoc or mysqli_fetch_assoc). IE: Instead of making 6 individual requests based on the meta_key, just pull all the data from that post_id:
SELECT * FROM cms_postmeta WHERE post_id=603
This is a much better way, and even this could be optimized to pull all of the posts data in one query instead of breaking it up over each post_id. You can, and should be able to pull all the data you need for the View in a single SQL query (using joins to pull data across multiple tables).
<b>This issue should be a priority fix for the next Views update.</b> Forming proper SQL queries and getting the total number of queries as low as possible should be the goal here.
TL:DR; Views is making too many individual queries to the MySQL database. Performance could be increased dramatically simply by using correctly formed SQL queries and appropriate PHP MySQL functions to sort filter and display the results.
Thanks for taking the time to read this. I'm totally open to questions to help this performance update get released as soon as possible.
