Tag: query

  • Quick Tip: Export WordPress SQL output via WP-CLI

    Quick Tip: Export WordPress SQL output via WP-CLI

    If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:

    /**
     * Runs a SQL query against the site database.
     *
     * ## OPTIONS
     *
     * 
     * : SQL Query to run.
     *
     * [--format=]
     * : Render output in a particular format.
     * ---
     * default: table
     * options:
     * - table
     * - csv
     * - json
     * - count
     * - yaml
     * ---
     *
     * [--dry-run=]
     * : Performa a dry run
     *
     * @subcommand sql
     */
    public function sql( $args, $assoc_args ) {
         global $wpdb;
     
         $sql     = $args[0];
         $format  = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
         $dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
     
         // Just some precautions.
         if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
             WP_CLI::error( 'Please do not modify the database with this command.' );
         }
     
         if ( 'false' !== $dry_run ) {
             WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: <code>EXPLAIN</code> of the query is below: https://mariadb.com/kb/en/explain/' ) );
             $sql = 'EXPLAIN ' . $sql;
         }
     
         // Fetch results from database.
         $results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB
     
         // Output data.
         WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
    }Code language: PHP (php)

    I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)

  • Better Caching in WordPress

    Better Caching in WordPress

    Caching data in WordPress is easy. Caching data in WordPress in a good and performant way takes a bit more work. For instance, many developers commonly use the Transients API to cache data. As the lowest common denominator in caching, this is okay. It’ll get the job done, even on a $10/year shared hosting plan. But what we should do instead is leverage the WP_Object_Cache functions to provide more functionality and better features.

    For instance, let’s say we want to cache the result of an external API request. One way we could do this would be this way:

    Note: These examples are terrible, but I hope they get the point across!

    function get_api( $value) {
    	$value = absint( $value );
    	$api_data = get_transient( 'example-api-data-' . $value );
    
    	if ( false === $api_data ) {
    		$api_data = file_get_contents( 'https://example.com/api/' . $value );
    		set_transient( 'example-api-data-' . $value, $api_data, HOUR_IN_SECONDS * 6 );
    	}
    
    	return json_decode( $api_data );
    }Code language: PHP (php)

    What’s one way we could make this better by using the WP_Object_Cache functions? Well, what happens if the API data structure changes, and you need to invalidate every cache value? It would be pretty hard to know the exact transient keys that you’d need to clear, and clearing the entire cache is a bit too nuclear for this (but it would work). Instead, wp_cache_*() could be used, which includes the ability to use a cache group that can be changed:

    function get_api( $value) {
    	$value = absint( $value );
    	$cache_group = 'example-api-data';
    
    	$api_data = wp_cache_get( $value, $cache_group );
    
    	if ( false === $api_data ) {
    		$api_data = file_get_contents( 'https://example.com/api/' . $value );
    		wp_cache_set( $value, $api_data, $cache_group, HOUR_IN_SECONDS * 6 );
    	}
    
    	return json_decode( $api_data );
    }Code language: PHP (php)

    With this, if we ever need to invalidate the cache for this API, we just need to change the $cache_group value, and all cache requests will be new.


    Another common theme I see is caching too much data. Let’s say you’re going to do a slow WP_Query, and want to cache the results for better performance:

    function get_new_posts() {
    	$posts = wp_cache_get( 'new-posts' );
    
    	if ( false === $posts ) {
    		$posts = new WP_Query( 'posts_per_page=5000' );
    		wp_cache_set( 'new-posts', $posts );
    	}
    
    	return $posts;
    }Code language: PHP (php)

    Sure, that’s fine and it’ll work but… the WP_Query object is huge!

    echo strlen( serialize( new WP_Query( 'posts_per_page=500' ) ) ); … 2,430,748

    That’s 2.5 megs of data needing to be transferred out of cache on every pageload. If your cache is accessed across the network on another server, this introduces more delay as it has to transfer. Also, some caching solutions might put a limit on the size of an individual cache object–which means that an object like this might never be cached!

    Instead, we can just grab the IDs of the posts, and do a second, much faster query:

    function get_new_posts() {
    	$post_ids = wp_cache_get( 'new-posts' );
    
    	if ( false === $posts ) {
    		$post_ids = new WP_Query( 'posts_per_page=5000&fields=ids' );
    		wp_cache_set( 'new-posts', $posts->posts );
    	}
    
    	$posts = new WP_Query( [ 'post__in' => $post_ids ] );
    
    	return $posts;
    }Code language: PHP (php)

    echo strlen( serialize( $posts->posts ) ); … only 88,838 bytes, that’s like a 96%-something difference!

    I had a few more ideas for this post, but it’s been sitting as a draft forever and I don’t remember. It’s possible this topic might be revisited some day 🙂

  • Query Caching (and a little extra)

    Query Caching (and a little extra)

    By default, WordPress does not cache WP_Query queries.  Doing so can greatly improve performance.  The way I do this is via the Advanced Post Cache plugin:

    By running this plugin (hopefully as an mu-plugin) with a persistent object cache, WP_Query calls, along with get_post() calls (only if suppress_filters is false) will be cached.

    Bonus!

    Now that we’re caching queries, here’s how I do a little extra caching to squeeze out a tiny bit more performance:

    <?php
    // By default Jetpack does not cache responses from Instagram oembeds.
    add_filter( 'instagram_cache_oembed_api_response_body', '__return_true' );
    
    // Cache WP Dashboard Recent Posts Query
    add_filter( 'dashboard_recent_posts_query_args', 'cache_dashboard_recent_posts_query_args', 10, 1 );
    function cache_dashboard_recent_posts_query_args( $query_args ) {
    	$query_args['cache_results'] = true;
    	$query_args['suppress_filters'] = false;
    	return $query_args;
    }
    
    // Cache WP Dashboard Recent Drafts Query
    add_filter( 'dashboard_recent_drafts_query_args', 'cache_dashboard_recent_drafts_query_args', 10, 1 );
    function cache_dashboard_recent_drafts_query_args( $query_args ) {
    	$query_args['suppress_filters'] = false;
    	return $query_args;
    }
    
    // Cache comment counts, https://github.com/Automattic/vip-code-performance/blob/master/core-fix-comment-counts-caching.php
    add_filter( 'wp_count_comments', 'wpcom_vip_cache_full_comment_counts', 10, 2 );
    function wpcom_vip_cache_full_comment_counts( $counts = false , $post_id = 0 ){
    	//We are only caching the global comment counts for now since those are often in the millions while the per page one is usually more reasonable.
    	if ( 0 !== $post_id ) {
    		return $counts;
    	}
    
    	$cache_key = "vip-comments-{$post_id}";
    	$stats_object = wp_cache_get( $cache_key );
    
    	//retrieve comments in the same way wp_count_comments() does
    	if ( false === $stats_object ) {
    		$stats = get_comment_count( $post_id );
    		$stats['moderated'] = $stats['awaiting_moderation'];
    		unset( $stats['awaiting_moderation'] );
    		$stats_object = (object) $stats;
    
    		wp_cache_set( $cache_key, $stats_object, 'default', 30 * MINUTE_IN_SECONDS );
    	}
    
    	return $stats_object;
    }
    
    // Cache monthly media array.
    add_filter( 'media_library_months_with_files', 'wpcom_vip_media_library_months_with_files' );
    function wpcom_vip_media_library_months_with_files() {
    	$months = wp_cache_get( 'media_library_months_with_files', 'extra-caching' );
    
    	if ( false === $months ) {
    		global $wpdb;
    		$months = $wpdb->get_results( $wpdb->prepare( "
    			SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
    			FROM $wpdb->posts
    			WHERE post_type = %s
    			ORDER BY post_date DESC
    			", 'attachment' )
    		);
    		wp_cache_set( 'media_library_months_with_files', $months, 'extra-caching' );
    	}
    
    	return $months;
    }
    
    add_action( 'add_attachment', 'media_library_months_with_files_bust_cache' );
    function media_library_months_with_files_bust_cache( $post_id ) {
    	if ( defined( 'WP_IMPORTING' ) && WP_IMPORTING ) {
    		return;
    	}
    
    	// What month/year is the most recent attachment?
    	global $wpdb;
    	$months = $wpdb->get_results( $wpdb->prepare( "
    			SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
    			FROM $wpdb->posts
    			WHERE post_type = %s
    			ORDER BY post_date DESC
    			LIMIT 1
    		", 'attachment' )
    	);
    
    	// Simplify by assigning the object to $months
    	$months = array_shift( array_values( $months ) );
    
    	// Compare the dates of the new, and most recent, attachment
    	if (
    		! $months->year == get_the_time( 'Y', $post_id ) &&
    		! $months->month == get_the_time( 'm', $post_id )
    	) {
    		// the new attachment is not in the same month/year as the
    		// most recent attachment, so we need to refresh the transient
    		wp_cache_delete( 'media_library_months_with_files', 'extra-caching' );
    	}
    }Code language: HTML, XML (xml)