[pridigital]

5 months, 2 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]

5 months, 2 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 5 months, 2 weeks ago by pridigital.

[pridigital]

5 months, 2 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 5 months, 2 weeks ago by pridigital. Reason: format

[wpsolr]

5 months, 2 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.