[wp-hackers] Optimizing a SQL query used to get all custom post metakeys

[Rarylson Freitas]

Hi Chris,

Thank you for your appointment!

--

*Rarylson Freitas*Engenheiro de Computação
Instituto Militar de Engenharia

On Thu, Jul 16, 2015 at 12:59 AM, Chris Christoff <hello at chriscct7.com>
wrote:

> -- Please reply above this line --
>
> -----------------------------------------------------------
> ## Chris replied, on Jul 15 @ 11:59pm (AMT):
>
> see https://core.trac.wordpress.org/ticket/24498#comment:10 [1]
>
> Links:
> ------
> [1] https://core.trac.wordpress.org/ticket/24498#comment:10
>
> --
> Chris Christoff
> hello at chriscct7.com
> http://www.chriscct7.com [1]
> @chriscct7
>
> Links:
> ------
> [1] http://www.chriscct7.com
>
>
> -----------------------------------------------------------
> ## wp-hackers at lists.automattic.com sent a message, on Jul 15 @ 11:53pm
> (AMT):
>
> Hi,
>
> I am responsible for a Wordpress site with more than 500.000 posts
> (more
> than half million posts).
>
> Recently, I've discovered some performance problems in the function
> meta_form, file wp-admin/includes/template.php. This function "Prints
> the
> form in the Custom Fields meta box".
>
> The problem was at line 674, where the SQL query was defined.
>
> I'm putting the raw (already processed) SQL query here:
>
> SELECT meta_key
>     FROM wp_postmeta
>     GROUP BY meta_key
>     HAVING meta_key NOT LIKE '_%'
>     ORDER BY meta_key
>     LIMIT 30
>
> To run this query, MySQL will sort all meta_key lines, including all
> custom
> fields and all the other fields (starting with '_'), unify them,
> select the
> desired fields, and them return them.
>
> The problem with its query is that it will run a sort over all of the
> entries (due to the GROUP BY part), and select the desired entries
> after
> the group by (HAVING part). A more efficient solution should consider
> excluding unnecessary entries before sorting/unifying them.
>
> A better query could be:
>
> SELECT UNIQUE meta_key
>     FROM wp_postmeta
>     WHERE meta_key NOT LIKE '_%'
>     ORDER BY meta_key
>     LIMIT 30
>
> In my case, this simply change gave us a performance improvement of 4x
> (from 8s to less then 2s).
>
> So, I'm proposing this patch to this file, and I hope that it can be
> useful
> to make the Wordpress project better.
>
> --- wp-admin/includes/template.php.orig    2015-07-16
> 00:22:28.000000000
> -0300
> +++ wp-admin/includes/template.php    2015-07-16 00:23:06.000000000
> -0300
> @@ -671,10 +671,9 @@
>       * @param int $limit Number of custom fields to retrieve. Default
> 30.
>       */
>      $limit = apply_filters( 'postmeta_form_limit', 30 );
> -    $sql = "SELECT meta_key
> +    $sql = "SELECT UNIQUE meta_key
>          FROM $wpdb->postmeta
> -        GROUP BY meta_key
> -        HAVING meta_key NOT LIKE %s
> +        WHERE meta_key NOT LIKE %s
>          ORDER BY meta_key
>          LIMIT %d";
>      $keys = $wpdb->get_col( $wpdb->prepare( $sql,
> $wpdb->esc_like( '_' ) .
> '%', $limit ) );
>
> --
>
> *Rarylson Freitas*Engenheiro de Computação
> Instituto Militar de Engenharia
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> -----------------------------------------------------------
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>