Database Query Performance Optimization for Slow Loading Results

  • pridigital
    Participant
    11 months, 3 weeks ago #37499

    Hi there,
    I’ve noticed that the loading time for the results is prolonged due to a specific issue outlined below.

    * # Time: 231117 19:27:23 # User@Host: 32a7300d6b3d49e68af0f321f2545b10[32a7300d6b3d49e68af0f321f2545b10] @ [10.73.2.13] # Thread_id: 94981 Schema: pantheon QC_hit: No # Query_time: 4.173086 Lock_time: 0.000074 Rows_sent: 286 Rows_examined: 1149332 # Rows_affected: 0 Bytes_sent: 11337 SET timestamp=1700249243; SELECT distinct meta_key, meta_value FROM wp_postmeta WHERE metakey LIKE ‘%’ AND metavalue like ‘field%’;

    My “Query Analysis” indicates that the “wp_postmeta” table lacks an index for this specific query. Moreover, given that it involves a fulltext “LIKE” search, indexing might not be feasible. The table contains nearly 1 million rows, contributing to the 4+ seconds response time.

    If the intention is to retrieve all results, it is advisable to exclude the “LIKE” search with a general “%” (wildcard), as this significantly impacts performance.

    Is there an alternative approach to achieving the desired outcome?

    pridigital
    Participant
    11 months, 3 weeks ago #37500

    Would adding something like this to the class-wpsolr-plugin-acf.php:219 file help?

    `

    if ( false === ( $results = get_transient( ‘some_unique_key’ ) ) ) {
    $sql = $wpdb->prepare( “SELECT distinct meta_key, meta_value
    FROM $wpdb->postmeta
    WHERE meta_key LIKE %s
    AND meta_value like %s”, ‘_%’, ‘field_%’ );
    $results = $wpdb->get_results( $sql );

    set_transient( ‘some_unique_key’, $results, 864000 );
    }
    `

    • This reply was modified 11 months, 3 weeks ago by pridigital.
    pridigital
    Participant
    11 months, 3 weeks ago #37502

    Here’s a better example

    
    $transient = 'some_unique_key';
    
    if ( false === ( $results = get_transient( $transient ) ) ) {
      $sql = $wpdb->prepare( "SELECT distinct meta_key, meta_value
    			  FROM $wpdb->postmeta
    			  WHERE meta_key LIKE %s
    			  AND meta_value like %s", array( '_%', 'field_%' ) );
      $results = $wpdb->get_results( $sql );
    
      set_transient( $transient, $results, DAY_IN_SECONDS );
    }
    
    • This reply was modified 11 months, 3 weeks ago by pridigital. Reason: format
    wpsolr
    Keymaster
    11 months, 3 weeks ago #37505

    Caching this part will prevent retrieving new ACF field values on your post types.

    You could disable the option “Replace labels with ACF labels” in the ACF PRO add-on.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.