Database Query Performance Optimization for Slow Loading Results
- pridigitalParticipant11 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?
pridigitalParticipant11 months, 3 weeks ago #37500Would 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.
pridigitalParticipant11 months, 3 weeks ago #37502Here’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
wpsolrKeymaster11 months, 3 weeks ago #37505Caching 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.
You must be logged in to reply to this topic.