[wp-trac] [WordPress Trac] #31071: media / post_mime_type related queries are very slow on larger sites

[WordPress Trac]

#31071: media / post_mime_type related queries are very slow on larger sites
------------------------------------------+--------------------------
 Reporter:  archon810                     |       Owner:  jnylen0
     Type:  defect (bug)                  |      Status:  closed
 Priority:  normal                        |   Milestone:  4.7.4
Component:  Media                         |     Version:  4.1
 Severity:  normal                        |  Resolution:  fixed
 Keywords:  has-patch commit fixed-major  |     Focuses:  performance
------------------------------------------+--------------------------

Comment (by eamusbarr):

 Database Optimization: Media files and post_mime_type queries often
 involve interacting with the site's database. If the database is not
 properly optimized, it can lead to slow performance. Ensure that the
 database tables are indexed correctly and that the queries are efficiently
 written.

 Hosting Resources: Larger sites with heavy media content may require
 significant hosting resources. If the hosting plan or server configuration
 is not capable of handling the load, it can result in slow queries.
 Consider upgrading to a more robust hosting plan or optimizing server
 settings for better performance.

 Caching: Implementing caching mechanisms can greatly improve the speed of
 media and post_mime_type queries. Utilize caching plugins or techniques to
 store frequently accessed data in memory, reducing the need to query the
 database every time.

 Content Delivery Network (CDN): Large sites often benefit from using a CDN
 to distribute media files across multiple servers geographically. This
 helps reduce the load on a single server and improves query response times
 by serving content from the nearest server to the user.

 Query Optimization: Analyze the specific queries related to media and
 post_mime_type and ensure they are optimized for efficiency. Review the
 database schema, indexes, and query execution plans to identify any
 bottlenecks or areas for improvement.

 Media File Size: If the media files themselves are very large, it can
 impact query performance. Consider optimizing and compressing images,
 videos, or other media files to reduce their size without significant loss
 of quality.

 Plugins and Themes: In some cases, poorly coded or resource-intensive
 plugins and themes can slow down media-related queries. Disable
 unnecessary plugins, choose lightweight themes, and regularly update all
 components to ensure optimal performance.

 Server-Side Caching: Implement server-side caching mechanisms such as
 opcode caching or object caching to store PHP execution results. This can
 help reduce the processing time required for generating dynamic pages.

 Query Limitations: If the number of media files or posts is extremely
 high, it can strain the query execution time. Consider implementing
 pagination or limit the number of results fetched in a single query to
 avoid overwhelming the server.

 Database Scaling: As a site grows, scaling the database infrastructure
 becomes important. Consider implementing sharding, replication, or
 distributed database systems to distribute the load and improve query
 performance.

 It's important to assess and optimize each of these factors to identify
 the specific cause of the slow performance related to media and
 post_mime_type queries on larger sites. It may require a combination of
 strategies to achieve significant improvements in query speed.



 https://xovacharging.com
 https://normareed.ca

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/31071#comment:83>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform