WordPress.org
Get WordPress

Making WordPress.org

Changeset 14917


Ignore:
Timestamp:
05/25/2026 08:09:58 AM (6 hours ago)
Author:
dd32
Message:

Translate: Switch the translation_status table out for one with a boolean has_pending strings field.

By using this denormalized has_pending column (= waiting > 0 OR fuzzy > 0), maintained by the stats writers, plus an extended index locale(locale, locale_slug, has_pending) the majority of "find projects that need attention" queries become a direct range scan over the ~0.4M pending rows instead of the full ~57M.

The end result of this is speeding up the Translate.w.org UI up significantly, with minimal downsides.

See discussion ​https://wordpress.slack.com/archives/C02QB8GMM/p1779270030102369

Location:
sites/trunk/wordpress.org/public_html/wp-content/plugins
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-custom-stats/stats/project.php

    r11767 r14917  
    9595
    9696        // Store the counts for these parent projects as the sum of their children.
    97         $sql = "INSERT INTO {$wpdb->project_translation_status} ( `project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `date_added`, `date_modified`)
     97        $sql = "INSERT INTO {$wpdb->project_translation_status} ( `project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `has_pending`, `date_added`, `date_modified`)
    9898        SELECT
    9999            p.parent_project_id as project_id,
    … …  
    101101            SUM( stats.all ) as `all`, SUM( stats.current ) as `current`, SUM( stats.waiting ) as `waiting`,
    102102            SUM( stats.fuzzy ) as `fuzzy`, SUM( stats.warnings ) as `warnings`, SUM( stats.untranslated ) as `untranslated`,
     103            ( SUM( stats.waiting ) > 0 OR SUM( stats.fuzzy ) > 0 ) as `has_pending`,
    103104            NOW() as `date_added`, NOW() as `date_modified`
    104105        FROM {$wpdb->project_translation_status} stats
    … …  
    112113            `waiting` = VALUES(`waiting`), `fuzzy` = VALUES(`fuzzy`),
    113114            `warnings` = VALUES(`warnings`), `untranslated` = VALUES(`untranslated`),
     115            `has_pending` = VALUES(`has_pending`),
    114116            `date_modified` = VALUES(`date_modified`);
    115117        ";
    … …  
    164166                $counts = $this->get_project_translation_counts( $project_id, $locale, $locale_slug );
    165167
    166                 $values[] = $wpdb->prepare( '(%d, %s, %s, %d, %d, %d, %d, %d, %d, %s, %s)',
     168                $values[] = $wpdb->prepare( '(%d, %s, %s, %d, %d, %d, %d, %d, %d, %d, %s, %s)',
    167169                    $project_id,
    168170                    $locale,
    … …  
    174176                    $counts['warnings'],
    175177                    $counts['untranslated'],
     178                    ( $counts['waiting'] > 0 || $counts['fuzzy'] > 0 ) ? 1 : 0,
    176179                    $now,
    177180                    $now
    … …  
    181184            // If we're processing a large batch, add them as we go to avoid query lengths & memory limits
    182185            if ( count( $values ) > 50 ) {
    183                 $wpdb->query( "INSERT INTO {$wpdb->project_translation_status} (`project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `date_added`, `date_modified` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VAlUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`), `date_modified`=VALUES(`date_modified`)" );
     186                $wpdb->query(
     187                    "INSERT INTO {$wpdb->project_translation_status}
     188                        ( `project_id`, `locale`, `locale_slug`,
     189                          `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `has_pending`,
     190                          `date_added`, `date_modified` )
     191                     VALUES " . implode( ', ', $values ) . "
     192                     ON DUPLICATE KEY UPDATE
     193                        `all`           = VALUES(`all`),
     194                        `current`       = VALUES(`current`),
     195                        `waiting`       = VALUES(`waiting`),
     196                        `fuzzy`         = VALUES(`fuzzy`),
     197                        `warnings`      = VALUES(`warnings`),
     198                        `untranslated`  = VALUES(`untranslated`),
     199                        `has_pending`   = VALUES(`has_pending`),
     200                        `date_modified` = VALUES(`date_modified`)"
     201                );
    184202                $values = array();
    185203            }
    … …  
    188206
    189207        if ( $values ) {
    190             $wpdb->query( "INSERT INTO {$wpdb->project_translation_status} (`project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `date_added`, `date_modified` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VALUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`), `date_modified`=VALUES(`date_modified`)" );
     208            $wpdb->query(
     209                "INSERT INTO {$wpdb->project_translation_status}
     210                    ( `project_id`, `locale`, `locale_slug`,
     211                      `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated`, `has_pending`,
     212                      `date_added`, `date_modified` )
     213                 VALUES " . implode( ', ', $values ) . "
     214                 ON DUPLICATE KEY UPDATE
     215                    `all`           = VALUES(`all`),
     216                    `current`       = VALUES(`current`),
     217                    `waiting`       = VALUES(`waiting`),
     218                    `fuzzy`         = VALUES(`fuzzy`),
     219                    `warnings`      = VALUES(`warnings`),
     220                    `untranslated`  = VALUES(`untranslated`),
     221                    `has_pending`   = VALUES(`has_pending`),
     222                    `date_modified` = VALUES(`date_modified`)"
     223            );
    191224        }
    192225    }
    … …  
    198231
    199232CREATE TABLE `gp_project_translation_status` (
    200   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     233  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    201234  `project_id` int(10) unsigned NOT NULL,
    202235  `locale` varchar(10) NOT NULL,
    … …  
    208241  `warnings` int(10) unsigned NOT NULL DEFAULT '0',
    209242  `untranslated` int(10) unsigned NOT NULL DEFAULT '0',
     243  `has_pending` tinyint(1) NOT NULL DEFAULT 0,
     244  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
     245  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    210246  PRIMARY KEY (`id`),
    211247  UNIQUE KEY `project_locale` (`project_id`,`locale`,`locale_slug`),
    212   KEY `all` (`all`),
    213   KEY `current` (`current`),
    214   KEY `waiting` (`waiting`),
    215   KEY `fuzzy` (`fuzzy`),
    216   KEY `warnings` (`warnings`),
    217   KEY `untranslated` (`untranslated`)
     248  KEY `locale` (`locale`,`locale_slug`,`has_pending`),
     249  KEY `date_added` (`date_added`),
     250  KEY `date_modified` (`date_modified`)
    218251) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    219252

  • sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-update-project-stats.php

    r14594 r14917  
    273273            'warnings'      => isset( $stats['warnings'] ) ? $stats['warnings'] : 0,
    274274            'untranslated'  => $stats['untranslated'],
     275            'has_pending'   => ( $stats['waiting'] > 0 || $stats['fuzzy'] > 0 ) ? 1 : 0,
    275276            'date_modified' => current_time( 'mysql' ),
    276277        );
    … …  
    306307                    '%d', // warnings
    307308                    '%d', // untranslated
     309                    '%d', // has_pending
    308310                    '%s', // date_modified
    309311                ),
    310312                array( '%d' )
    311313            );
    312         } 
     314        }
    313315        // else {
    314316        //  // Insert new record
    315317        //  $data['date_added'] = current_time( 'mysql' );
    316            
     318
    317319        //  $result = $wpdb->insert(
    318320        //      "{$wpdb->project_translation_status}",
    … …  
    328330        //          '%d', // warnings
    329331        //          '%d', // untranslated
     332        //          '%d', // has_pending
    330333        //          '%s', // date_added
    331334        //          '%s', // date_modified

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

    r14915 r14917  
    785785            if ( $can_approve_for_all ) {
    786786                // The current user can approve for all projects, so just grab all with any waiting strings.
    787                 $stats_where = ' AND ( stats.waiting > 0 OR stats.fuzzy > 0 )';
     787                $stats_where = ' AND stats.has_pending = 1';
    788788                $tp_where    = $base_level_project_sql;
    789789            } elseif ( $allowed_projects || $allowed_base_level_projects ) {
    790                 $stats_where = ' AND ( stats.waiting > 0 OR stats.fuzzy > 0 )';
     790                $stats_where = ' AND stats.has_pending = 1';
    791791                $tp_where    = ' AND ( (';
    792792
    … …  
    885885
    886886            case 'strings-waiting-and-fuzzy':
    887                 $stats_where    .= ' AND ( stats.waiting > 0 OR stats.fuzzy > 0 )';
    888                 $filter_order_by = "tp.path LIKE 'wp/%%' AND (stats.fuzzy + stats.waiting) > 0 DESC, (stats.fuzzy + stats.waiting) $sort_order, tp.name ASC";
     887                $stats_where    .= ' AND stats.has_pending = 1';
     888                $filter_order_by = "tp.path LIKE 'wp/%%' DESC, (stats.fuzzy + stats.waiting) $sort_order, tp.name ASC";
    889889                break;
    890890
    891891            case 'strings-waiting-and-fuzzy-by-modified-date':
    892                 $stats_where    .= ' AND ( stats.waiting > 0 OR stats.fuzzy > 0 ) AND stats.date_modified > "0000-00-00 00:00:00"';
     892                $stats_where    .= ' AND stats.has_pending = 1 AND stats.date_modified > "0000-00-00 00:00:00"';
    893893                $filter_order_by = "stats.date_modified $sort_order, tp.name ASC";
    894894                break;
Note: See TracChangeset for help on using the changeset viewer.