Changeset 8553

Timestamp:

03/29/2019 06:11:54 PM (5 years ago)

Author:

ocean90

Message:

Translate: Query existing locales for the homepage in a more performant way.

GP::$translation_set->existing_locales(); uses DISTINCT() on all translation sets which produces query timeouts. Instead, query only the existing locales of the main WordPress core project (wp/dev).

File:

• sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-routes/inc/class-plugin.php (modified) (3 diffs)

sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-routes/inc/class-plugin.php

@@ -208 +208 @@
             FROM {$wpdb->project_translation_status}
             WHERE `project_id` = %d AND `locale_slug` = %s",
-            2, 'default' // 2 = wp/dev
+            2, // 2 = wp/dev
+            'default'
         ), OBJECT_K );
 
@@ -228 +229 @@
         }
 
-        $locales   = GP::$translation_set->existing_locales();
+        $locales   = $this->get_existing_locales();
         $db_counts = $wpdb->get_results(
             "SELECT `locale`, COUNT( DISTINCT user_id ) as `count` FROM {$wpdb->user_translations_count} WHERE `accepted` > 0 GROUP BY `locale`",
@@ -251 +252 @@
 
     /**
+     * Retrieves existing locales of the main wp/dev project.
+     *
+     * Much faster alternative to `GP::$translation_set->existing_locales();` since it avoids
+     * the expensive DISTINCT.
+     *
+     * @return array List of GlotPress locales.
+     */
+    private function get_existing_locales() {
+        global $wpdb;
+
+        return $wpdb->get_col(
+            $wpdb->prepare(
+                "SELECT locale FROM {$wpdb->gp_translation_sets} WHERE `project_id` = %d and slug = %s",
+                2, // 2 = wp/dev
+                'default'
+            )
+        );
+    }
+
+    /**
      * Updates cache for existing locales.
      */
     public function update_existing_locales_cache() {
-        $existing_locales = GP::$translation_set->existing_locales();
-
+        $existing_locales = $this->get_existing_locales();
         if ( ! $existing_locales ) {
             return;