Skip Navigation

[Resolved] Views – Too many MySQL Queries killing performance and load times

This support ticket is created 9 years, 8 months ago. There's a good chance that you are reading advice that it now obsolete.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

Sun Mon Tue Wed Thu Fri Sat
- 10:00 – 19:00 10:00 – 19:00 10:00 – 19:00 10:00 – 19:00 10:00 – 19:00 -
- - - - - - -

Supporter timezone: Europe/Madrid (GMT+01:00)

Tagged: 

This topic contains 12 replies, has 7 voices.

Last updated by Jevgenijs 8 years, 5 months ago.

Assigned support staff: Caridad.

Author
Posts
#74566

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.

#74593

Dear Bryan,

We use WordPress functions to get the value of custom fields (get_post_meta()). It is this function provided by WordPress that is generating a single query for every custom field.

One thing to keep in mind is that MySQL is very good at doing these small queries, in your screenshot you can see that they are taking less than one thousandth of a second each. Taking a total of 0.3 seconds for all queries, so I think you should be looking somewhere else for the performance problems.

Im going to forward this issue to the developers to see if they can think of a way to optimize this, maybe get_post_custom()? I will get back to you when I have more information.

Please let me know if you are satisfied with my answer and if I can help you with any other questions you might have.

Regards,
Caridad

#75761

After doing some investigating, it appears that it's more a problem with the total number of Types fields you have per post, coupled with the large amount of queries being made.

Believe it or not, the site I'm building actually requires and uses 205 fields for one post type, and I believe this is where the slow down is coming from.

I was able to replicate this issue on Discover WP:

Site with 35 Entries, 5 Fields per Table, 5 total fields:
hidden link

Site with 35 Entries, 5 fields per table, 200 total fields:
hidden link

Results (5 Fields on left, 200 on Right):
hidden link

It more than doubles the time required to retrieve the page when there is 200 fields per post. This is due to the structure of the query, and that is uses the meta_key as a secondary index to retrieve the specific form data. For example:

SELECT * FROM cms_postmeta WHERE post_id=613 - 0.0045 sec - This grabs all the non-empty rows for post 613.

SELECT * FROM cms_postmeta WHERE post_id=613 AND meta_key='wpcf-client-address' - 0.0034 sec - This grabs just the client address field

Finding the post via the post_id is fairly quick, but when you are then sorting through 200 + meta_key's that require string comparison, it will definitely slow the query down. Now imagine this scenario over a 100 times per page load vs only a handful. It's pretty easy to see that the database queries are the cause of the performance issues.

Using get_post_custom (or get_post_meta with only an ID arguement) is a <b>MUCH</b> better alternative to using get_post_meta for each individual field.

Right now Views is using a query per field, meaning only a single string can be the result of each query. It should be the opposite. The query should grab all of the meta values of the post, and then the resulting array should be looped over in PHP to extract the field values and display them to the user.

Approaching it this way would dramatically improve performance for the "bigger scale" applications that can be built in Types + Views, but it will equally improve performance for the smaller use cases as well. It would take tons of stress off the MySQL server, meaning load times would be overall faster and more responsive.

#75897

Dear Bryan,

Thanks for the details, its quite clear that the amount of queries is the cause. I will forward this information to the developers and get back to you when I have more information.

Please let me know if you are satisfied with my answer and if I can help you with any other questions you might have.

Regards,
Caridad

#76136

Thanks Caridad,

Let me know if you need any of the information for the Discover WP's or the site I'm developing right now.

Looking forward to getting this optimized!

#76410

Dear Bryan,

The developers are looking into this right now and discussing several solutions. Im not sure if it will be included in next version or the following, but we cant do anything more from here. I will close this thread and you can open a new one if you find problems in next release.

Please let me know if you are satisfied with my answer and if I can help you with any other questions you might have.

Regards,
Caridad

#150595

Hi Bryan...did you ever get this resolved? I a having a similar issue.

#177859

How this thread is getting {Resolved} tag? Was this issue somehow fixed?

#210314

@bryan: I'm having a similar issue with performance on a custom post type with dozens of custom fields.

If I understand you correctly, I could speed up page load by using custom php-coded templates for archives and single posts instead of using views templates with shortcodes?

Please correct me if I'm wrong, any feedback would be greatly appreciated!

#210455

@ William & Lukasz: As far as I know this has *NOT* been resolved. Performance still seems to remain the same as when I posted this thread.

@steffenr: Yes, that could be one solution. Essentially you wouldn't use Views to display your custom content created through Types / CRED.

Problem with going that route is you lose the useful functionality built into Views such as sorting, search, and pagination.

If you do go about coding custom templates, please post them here for others to see, and maybe the dev's can get some more ideas on how to reduce the MySQL load in Views.

#210477

For perspective, I have over 17,000 posts with nearly 60 custom fields per post as well as querying Amazon for ads specific to that post. With W3 Total Cache installed and using APC caching, object caching, browser caching and Varnish with reverse proxy my front-end post load times are around 2.38 seconds and that's with over 148 queries per post. Plugin overhead is around .5 of a second. This is on a PAAS with 1Gb RAM and 16PHP's processes available to it. I'm just wondering if you have explored appropriate caching options?

#210533

@johnm-2: While there is plenty one can do in the way of caching, I'm not strictly trying to improve load times.

I've pointed out how Views inefficiently accesses the database, and other more effective methods are readily available in WordPress.

There is still plenty of strides Views can make in terms of optimizing performance and reducing calls to the database. Caching on top of that is icing on the cake.

#228626

This is not resolved. Performance of Views rendering something from Types is awful.