php - How to optimize and reduce excessive database queries

admin管理员组

文章数量:1122846

After we have run our codes and is functioning the way we want, something else faces us in the future and thats speed and scalability. Example a blog site must query the database to pull blog contents from the wp blog table, like wise our oroducts post and pages itself, and if we run user dashboard we still query if user is logged in and previledges.
Most times this type of query are executed on every page load. Is there a way to handle this and serve cached content without having to query the data base all times on page load and how reliable can cached content be? Or was mysql built as a query king to absorb any kind of query every secs without feeling headache, please what is the best approach seeing the life of our website depends on our database but yet no function gets done without querying the database.

After we have run our codes and is functioning the way we want, something else faces us in the future and thats speed and scalability. Example a blog site must query the database to pull blog contents from the wp blog table, like wise our oroducts post and pages itself, and if we run user dashboard we still query if user is logged in and previledges.
Most times this type of query are executed on every page load. Is there a way to handle this and serve cached content without having to query the data base all times on page load and how reliable can cached content be? Or was mysql built as a query king to absorb any kind of query every secs without feeling headache, please what is the best approach seeing the life of our website depends on our database but yet no function gets done without querying the database.

• php
• mysql

Share Improve this question asked Jun 5, 2020 at 15:01 pandglobalpandglobal 4311 silver badge99 bronze badges 3

• You can also sign up for ElasticPress.io and hook their functionality directly into some of your queries. What this would do is alleviate some of the burden on your server and db - you could set it so that posts, pages and products use ElasticPress in their queries but keep orders and the admin dashboard using standard WP queries. I tested this on a page that was running 17 queries based on taxonomies, and adding EP reduced the query count to 5 with zero impact on load times. Only problem is that it can get pricey. – Tony Djukic Commented Jun 5, 2020 at 15:47
• Really sounds interesting but how secured can this be and is it not a bridge of privacy sending users data to a third party, i love something i can control on my own so i can take the blame when things happen – pandglobal Commented Jun 5, 2020 at 16:32
• Well you have the ability to control which post_types are indexed vs. which aren't. The pages, posts and products are public anyway. You can set the orders to not be indexed as you're not using EP with them anyway. Additionally, you can download ElasticSearch and install it on your own servers, but then you have manage and maintain it all yourself and from past experience you need a good devops and systems admin team for that. – Tony Djukic Commented Jun 5, 2020 at 20:33

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You can use a combination of caching plugin and appropriately coded themes and plugins to cache query results and speed things up. Yes, eventually if you are running many queries, a site can get bogged down. This typically happens when query-intensive themes or plugins are used.

To start investigating your own site's queries, there are query monitoring plugins available - try installing one and you will see what queries are running where. From there, you can determine whether plugins or the theme are running the most intensive queries, and either try out alternative themes/plugins or code a child theme to reduce the load. You may also find that using (and properly configuring) a caching plugin may relieve much of the burden. It varies site to site, depending on the underlying server architecture, where visitors are coming from, how many visits you're getting, and what exactly your site is trying to do.

本文标签： phpHow to optimize and reduce excessive database queries