多语言用户数据查询语句性能优化及索引效果验证问询

Great question! Let's break down your problem and walk through actionable optimizations step by step, along with better alternatives to ANY_VALUE()/MAX().

一、当前查询仍有优化空间 #

While adding the deleted_at index fixed the full table scan, your execution plan still reveals two key areas for improvement:

• The lng table shows Using temporary; Using filesort in the Extra column, which means your GROUP BY users.id is forcing MySQL to create temporary tables and sort data—both resource-heavy operations.
• The user_translations table is using single-column indexes, which require "table lookups" to pull the name fields after locating the matching rows, adding unnecessary IO overhead.

二、可行的性能优化手段 #

1. Create a covering composite index for user_translations #

Your query pulls multiple name fields from user_translations using user_id + language_id as the join condition. A composite index that includes both the join columns and the required name fields will enable covering queries (no need to access the main table at all):

CREATE INDEX idx_user_trans_user_lang_full ON user_translations(
    user_id, language_id, first_name, second_name, third_name, last_name
);

This index will:

• Quickly locate the correct translation record for a user+language pair
• Serve all name fields directly from the index, eliminating costly table lookups

2. Optimize the users table index #

Your current deleted_at single-column index can be enhanced to cover both the filter and grouping logic:

CREATE INDEX idx_users_deleted_id_country ON users(
    deleted_at, id, country_id
);

This index offers three benefits:

• Fast filtering of non-deleted users (deleted_at IS NULL)
• Covers the GROUP BY users.id clause, eliminating Using filesort
• Includes country_id, so MySQL doesn't need to read the main table to join with countries

3. Adjust JOIN order to reduce temporary table overhead #

Your execution plan starts with the lng table, but forcing MySQL to first scan the users table (your main dataset) can reduce the volume of data processed in subsequent joins:

SELECT STRAIGHT_JOIN users.*, 
       COALESCE(ANY_VALUE(trans.first_name), ANY_VALUE(fb_trans.first_name)) AS first_name,
       COALESCE(ANY_VALUE(trans.second_name), ANY_VALUE(fb_trans.second_name)) AS second_name,
       COALESCE(ANY_VALUE(trans.third_name), ANY_VALUE(fb_trans.third_name)) AS third_name,
       COALESCE(ANY_VALUE(trans.last_name), ANY_VALUE(fb_trans.last_name)) AS last_name,
       CONCAT_WS(' ', 
           COALESCE(ANY_VALUE(trans.first_name), ANY_VALUE(fb_trans.first_name)),
           COALESCE(ANY_VALUE(trans.second_name), ANY_VALUE(fb_trans.second_name)),
           COALESCE(ANY_VALUE(trans.third_name), ANY_VALUE(fb_trans.third_name)),
           COALESCE(ANY_VALUE(trans.last_name), ANY_VALUE(fb_trans.last_name))
       ) AS full_name,
       countries.flag AS country_flag,
       countries.code AS country_code 
FROM `users` 
INNER JOIN `countries` ON `countries`.`id` = `users`.`country_id`
INNER JOIN `user_translations` AS `fb_trans` ON `fb_trans`.`user_id` = `users`.`id`
INNER JOIN `languages` AS `fb_lng` ON `fb_lng`.`id` = `fb_trans`.`language_id`
INNER JOIN `languages` AS `lng` ON `lng`.`lang_code` = 'ar'
LEFT JOIN `user_translations` AS `trans` ON `trans`.`user_id` = `users`.`id` AND `trans`.`language_id` = `lng`.`id`
WHERE `users`.`deleted_at` IS NULL 
GROUP BY `users`.`id`;

STRAIGHT_JOIN tells MySQL to follow the exact table order in your SQL, starting with filtering non-deleted users first to shrink the dataset early.

4. Simplify full_name calculation to avoid redundant aggregation #

You’re repeating the same COALESCE(ANY_VALUE(...)) calls four times for full_name. Use a CTE to calculate these values once, then reuse them:

WITH user_name_data AS (
    SELECT 
        users.id,
        COALESCE(ANY_VALUE(trans.first_name), ANY_VALUE(fb_trans.first_name)) AS first_name,
        COALESCE(ANY_VALUE(trans.second_name), ANY_VALUE(fb_trans.second_name)) AS second_name,
        COALESCE(ANY_VALUE(trans.third_name), ANY_VALUE(fb_trans.third_name)) AS third_name,
        COALESCE(ANY_VALUE(trans.last_name), ANY_VALUE(fb_trans.last_name)) AS last_name
    FROM `users` 
    INNER JOIN `user_translations` AS `fb_trans` ON `fb_trans`.`user_id` = `users`.`id`
    INNER JOIN `languages` AS `fb_lng` ON `fb_lng`.`id` = `fb_trans`.`language_id`
    INNER JOIN `languages` AS `lng` ON `lng`.`lang_code` = 'ar'
    LEFT JOIN `user_translations` AS `trans` ON `trans`.`user_id` = `users`.`id` AND `trans`.`language_id` = `lng`.`id`
    WHERE `users`.`deleted_at` IS NULL 
    GROUP BY `users`.`id`
)
SELECT 
    u.*,
    un.first_name,
    un.second_name,
    un.third_name,
    un.last_name,
    CONCAT_WS(' ', un.first_name, un.second_name, un.third_name, un.last_name) AS full_name,
    c.flag AS country_flag,
    c.code AS country_code
FROM users u
JOIN user_name_data un ON u.id = un.id
JOIN countries c ON u.country_id = c.id
WHERE u.deleted_at IS NULL;

CONCAT_WS is better than CONCAT here—it automatically ignores NULL values, so you won’t get extra spaces if a name part is missing.

三、Better alternatives to ANY_VALUE()/MAX() #

Using aggregation functions to bypass only_full_group_by works, but window functions are a cleaner, more efficient approach that lets you avoid GROUP BY entirely:

Option 1: Use a default_language_id field (recommended if possible) #

If you can add a default_language_id column to the users table (set during registration), you can directly fetch the default translation without extra joins:

WITH default_translations AS (
    SELECT 
        ut.user_id,
        ut.first_name, ut.second_name, ut.third_name, ut.last_name
    FROM user_translations ut
    JOIN users u ON ut.user_id = u.id
    WHERE ut.language_id = u.default_language_id
),
target_translations AS (
    SELECT 
        ut.user_id,
        ut.first_name, ut.second_name, ut.third_name, ut.last_name
    FROM user_translations ut
    JOIN languages l ON ut.language_id = l.id
    WHERE l.lang_code = 'ar'
)
SELECT 
    u.*,
    COALESCE(tt.first_name, dt.first_name) AS first_name,
    COALESCE(tt.second_name, dt.second_name) AS second_name,
    COALESCE(tt.third_name, dt.third_name) AS third_name,
    COALESCE(tt.last_name, dt.last_name) AS last_name,
    CONCAT_WS(' ', 
        COALESCE(tt.first_name, dt.first_name),
        COALESCE(tt.second_name, dt.second_name),
        COALESCE(tt.third_name, dt.third_name),
        COALESCE(tt.last_name, dt.last_name)
    ) AS full_name,
    c.flag AS country_flag,
    c.code AS country_code
FROM users u
JOIN default_translations dt ON u.id = dt.user_id
LEFT JOIN target_translations tt ON u.id = tt.user_id
JOIN countries c ON u.country_id = c.id
WHERE u.deleted_at IS NULL;

Option 2: Use ROW_NUMBER() to get the first inserted translation #

If you don’t have a default_language_id field, use a window function to rank translations by creation time and pick the first one as the default:

WITH ranked_translations AS (
    SELECT 
        ut.user_id,
        ut.first_name, ut.second_name, ut.third_name, ut.last_name,
        ROW_NUMBER() OVER (PARTITION BY ut.user_id ORDER BY ut.created_at ASC) AS rank
    FROM user_translations ut
),
default_translations AS (
    SELECT * FROM ranked_translations WHERE rank = 1
),
target_translations AS (
    SELECT 
        ut.user_id,
        ut.first_name, ut.second_name, ut.third_name, ut.last_name
    FROM user_translations ut
    JOIN languages l ON ut.language_id = l.id
    WHERE l.lang_code = 'ar'
)
SELECT 
    u.*,
    COALESCE(tt.first_name, dt.first_name) AS first_name,
    -- Repeat for other name fields...
    CONCAT_WS(' ', 
        COALESCE(tt.first_name, dt.first_name),
        COALESCE(tt.second_name, dt.second_name),
        COALESCE(tt.third_name, dt.third_name),
        COALESCE(tt.last_name, dt.last_name)
    ) AS full_name,
    c.flag AS country_flag,
    c.code AS country_code
FROM users u
JOIN default_translations dt ON u.id = dt.user_id
LEFT JOIN target_translations tt ON u.id = tt.user_id
JOIN countries c ON u.country_id = c.id
WHERE u.deleted_at IS NULL;

This approach eliminates GROUP BY, temporary tables, and sorting entirely, making the query both faster and easier to read.

四、Bonus Tips #

• If your system has a fixed default language (e.g., English), hardcode its ID in the query instead of joining fb_lng—this removes an unnecessary join layer.
• Periodically run OPTIMIZE TABLE on large tables like users and user_translations to clean up fragmentation and improve index performance.

内容的提问来源于stack exchange，提问作者HasanAlyazidi