0

The problem is that I have a relatively simple post type with two custom fields that need to sort depending on those fields. I have all of this hooked in to pre_get_posts (along with some other sort methods). It works, but this particularly query is hanging for about 30 seconds and then giving a "503 Service Unavailable" message.


I have a custom post type called "evaluation" and have a few ACF fields:

  1. workshop_or_individual_clients
    • Dropdown: "A workshop" or " Individual clients"
  2. workshop_completion_date
    • Number: "20190321" (Only if "A workshop", otherwise NOT EXISTS)

On the dashboard I have added a custom column for workshop date, and would like to sort it so that:

  1. Individual clients are listed first, by publish date
  2. Workshops are listed afterwards, sorted by their workshop date (and then publish date if the same)

Here is the code that sorts the dashboard posts. The orderby parameter is "eval_workshop_date". The goal is to put Individual on top and Workshops below. The workshops should sort by their workshop date (custom field), but not individuals because they do not have a date.

switch( $_GET['orderby'] ) {

  case 'eval_workshop_date':
    $mq = array(
        'relation' => 'OR',

        'individual' => array(
            'key' => 'workshop_or_individual_clients',
            'value' => 'A workshop',
            'compare' => '!='
        ),

        'workshop_date' => array(
            'key' => 'workshop_completion_date',
            'value' => '',
            'compare' => '!='
        ),
    );

    // Add existing meta query back in if it exists
    if ( $query->get( 'meta_query' ) ) {
        $mq = array(
            'relation' => 'AND',
            $mq,
            $query->get( 'meta_query' )
        );
    }

    // Set the meta query
    $query->set( 'meta_query', $mq );

    // Change it to order by our new meta query options
    $query->set( 'orderby', array(
        'individual' => 'DESC',
        'workshop_date' => 'DESC'
    ));
  break;
}

Now that all works and is perfectly reasonable meta_query and orderby parameters.

But if you then search the posts for "Workshop 5" which has about 20 results, suddenly the page hangs and then gives that 503 error.

However if you simply remove "GROUP BY wp_posts.ID", then the query is successful in a mere 0.037 seconds!

SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.*
FROM   wp_posts
       INNER JOIN wp_postmeta
               ON ( wp_posts.ID = wp_postmeta.post_id )
       INNER JOIN wp_postmeta AS mt1
               ON ( wp_posts.ID = mt1.post_id )
       INNER JOIN wp_postmeta AS a
               ON ( a.post_id = wp_posts.ID )
       INNER JOIN wp_postmeta AS b
               ON ( ( b.meta_id = a.meta_id + @@auto_increment_increment )
                    AND ( b.post_id = wp_posts.ID ) )
       INNER JOIN wp_posts AS c
               ON ( ( c.post_type = 'acf-field' )
                    AND ( ( c.post_content LIKE '%:"text"%' )
                           OR ( c.post_content LIKE '%:"textarea"%' )
                           OR ( c.post_content LIKE '%:"wysiwyg"%' ) ) )
WHERE  1 = 1
       AND ( ( ( c.post_name = b.meta_value )
               AND ( a.meta_value LIKE '%Workshop%' )
               AND ( a.meta_value LIKE '%5%' ) )
              OR ( ( ( wp_posts.post_title LIKE '%Workshop%' )
                      OR ( wp_posts.post_content LIKE '%Workshop%' )
                      OR ( wp_posts.post_excerpt LIKE '%Workshop%' ) )
                   AND ( ( wp_posts.post_title LIKE '%5%' )
                          OR ( wp_posts.post_content LIKE '%5%' )
                          OR ( wp_posts.post_excerpt LIKE '%5%' ) ) ) )
       AND ( ( wp_postmeta.meta_key = 'workshop_or_individual_clients'
               AND wp_postmeta.meta_value != 'A workshop' )
              OR ( mt1.meta_key = 'workshop_completion_date'
                   AND mt1.meta_value != '' ) )
       AND wp_posts.post_type = 'dtl-evaluation'
       AND ( wp_posts.post_status = 'publish'
              OR wp_posts.post_status = 'acf-disabled'
              OR wp_posts.post_status = 'future'
              OR wp_posts.post_status = 'draft'
              OR wp_posts.post_status = 'pending'
              OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.ID
ORDER  BY CAST(wp_postmeta.meta_value AS CHAR) DESC,
          CAST(mt1.meta_value AS CHAR) DESC,
          wp_posts.post_date DESC
LIMIT  0, 20 

Finally, the table structure for wp_posts is below. I was thinking that maybe the ID was not an index -- but it is. It is the primary key. I even compared to a fresh new WP install and the structure is the same.

+-----------------------+------------------------------------+---------+
| Column                | Type                               | Comment |
+-----------------------+------------------------------------+---------+
| ID                    | bigint(20) unsigned Auto Increment |         |
| post_author           | bigint(20) unsigned [0]            |         |
| post_date             | datetime [0000-00-00 00:00:00]     |         |
| post_date_gmt         | datetime [0000-00-00 00:00:00]     |         |
| post_content          | longtext                           |         |
| post_title            | text                               |         |
| post_excerpt          | text                               |         |
| post_status           | varchar(20) [publish]              |         |
| comment_status        | varchar(20) [open]                 |         |
| ping_status           | varchar(20) [open]                 |         |
| post_password         | varchar(255) []                    |         |
| post_name             | varchar(200) []                    |         |
| to_ping               | text                               |         |
| pinged                | text                               |         |
| post_modified         | datetime [0000-00-00 00:00:00]     |         |
| post_modified_gmt     | datetime [0000-00-00 00:00:00]     |         |
| post_content_filtered | longtext                           |         |
| post_parent           | bigint(20) unsigned [0]            |         |
| guid                  | varchar(255) []                    |         |
| menu_order            | int(11) [0]                        |         |
| post_type             | varchar(20) [post]                 |         |
| post_mime_type        | varchar(100) []                    |         |
| comment_count         | bigint(20) [0]                     |         |
+-----------------------+------------------------------------+---------+

Keys:
+---------+---------------------------------------+
| PRIMARY | ID                                    |
+---------+---------------------------------------+
| INDEX   | post_name(191)                        |
| INDEX   | post_type, post_status, post_date, ID |
| INDEX   | post_parent                           |
| INDEX   | post_author                           |
+---------+---------------------------------------+


As a temporary workaround (aka: hack) I added the following code, which resolves the problem. I'm not happy about this solution though!

function wp_so_336227_pre_get_posts_admin( $query ) {

     // All of the other orderby parameters are located here.

     /**
     * Fix for hanging when searching the evaluation table:
     * Problem is that "group by wp_posts.ID" causes the query to fail. No idea why. This just removes the "group by" clause entirely.
     */
    if ( !empty($_GET['s']) ) {
        add_action( 'posts_clauses_request', 'wp_so_336227_remove_groupby' );
    }
}

function wp_so_336227_remove_groupby( $q ) {
    $q['groupby'] = '';
    return $q;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.