Optimizing a SQL query used to get all custom post metakeys

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimizing a SQL query used to get all custom post metakeys

Rarylson Freitas
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
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Optimizing a SQL query used to get all custom post metakeys

chriscct7
-- 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
[hidden email]
http://www.chriscct7.com [1]
@chriscct7

Links:
------
[1] http://www.chriscct7.com


-----------------------------------------------------------
## [hidden email] 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
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers

-----------------------------------------------------------

_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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
> [hidden email]
> http://www.chriscct7.com [1]
> @chriscct7
>
> Links:
> ------
> [1] http://www.chriscct7.com
>
>
> -----------------------------------------------------------
> ## [hidden email] 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
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
> -----------------------------------------------------------
>
> _______________________________________________
> wp-hackers mailing list
> [hidden email]
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>
_______________________________________________
wp-hackers mailing list
[hidden email]
http://lists.automattic.com/mailman/listinfo/wp-hackers