Optimizing Multilingual User Data Query

Question

I hope you are doing well.

I have the following query to get the users with their translated names and country information which needs speed optimization:

SELECT 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(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 `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`
INNER JOIN `countries` ON `countries`.`id` = `users`.`country_id`
WHERE `users`.`deleted_at` IS NULL
GROUP BY `users`.`id`;

A user (a row in users table) must be inserted using a transaction along with a translation (in user_translations table) which will be the first translation and the default/fallback (fb_trans and fb_lng joins)

Let's say a user has registered on our app when the app language was set to "en" (languages.id = 1). That user will be added to users table and user_translations table with language_id = 1 (en). That user's default translation is English.

Then the language changed on the app from "en" to "ar" (languages.id = 2) (Arabic), the query will try to get the Arabic translation of the user if existing, otherwise, it will get the user's first/default/fallback translation which is the English one.

Explain Query

Explain query before adding an index for deleted_at column in users table (The query triggers log_queries_not_using_indexes):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	lng	NULL	ref	languages_lang_code_index	languages_lang_code_index	767	const	1	100.00	Using index; Using temporary; Using filesort
1	SIMPLE	users	NULL	ALL	PRIMARY,users_uuid_unique,users_country_id_foreign,users_city_id_foreign,users_district_id_foreign	NULL	NULL	NULL	16669	10.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	countries	NULL	eq_ref	PRIMARY	PRIMARY	4	db_name.users.country_id	1	100.00	NULL
1	SIMPLE	fb_trans	NULL	ref	user_translations_user_id_index,user_translations_language_id_index	user_translations_user_id_index	8	db_name.users.id	1	100.00	NULL
1	SIMPLE	fb_lng	NULL	eq_ref	PRIMARY	PRIMARY	8	db_name.fb_trans.language_id	1	100.00	Using index
1	SIMPLE	trans	NULL	ref	user_translations_user_id_index,user_translations_language_id_index	user_translations_user_id_index	8	db_name.users.id	1	100.00	Using where

# Test 1: Query_time: 0.277862  Lock_time: 0.008804 Rows_sent: 16313  Rows_examined: 114731
# Test 2: Query_time: 0.218047  Lock_time: 0.000165 Rows_sent: 16313  Rows_examined: 114731
# Test 3: Query_time: 0.220287  Lock_time: 0.000153 Rows_sent: 16313  Rows_examined: 114731

Explain query after adding an index for deleted_at column in users table (Now the query does not trigger log_queries_not_using_indexes which is nice):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	lng	NULL	ref	languages_lang_code_index	languages_lang_code_index	767	const	1	100.00	Using index; Using temporary; Using filesort
1	SIMPLE	users	NULL	ref	PRIMARY,users_uuid_unique,users_country_id_foreign,users_city_id_foreign,users_district_id_foreign,tmp_idx_deleted_at	tmp_idx_deleted_at	5	const	8334	100.00	Using index condition; Using where
1	SIMPLE	countries	NULL	eq_ref	PRIMARY	PRIMARY	4	db_name.users.country_id	1	100.00	NULL
1	SIMPLE	fb_trans	NULL	ref	user_translations_user_id_index,user_translations_language_id_index	user_translations_user_id_index	8	db_name.users.id	1	100.00	NULL
1	SIMPLE	fb_lng	NULL	eq_ref	PRIMARY	PRIMARY	8	db_name.fb_trans.language_id	1	100.00	Using index
1	SIMPLE	trans	NULL	ref	user_translations_user_id_index,user_translations_language_id_index	user_translations_user_id_index	8	db_name.users.id	1	100.00	Using where

# Test 1: Query_time: 0.248500  Lock_time: 0.006678 Rows_sent: 16313  Rows_examined: 114458
# Test 2: Query_time: 0.268973  Lock_time: 0.000258 Rows_sent: 16313  Rows_examined: 114458
# Test 3: Query_time: 0.242067  Lock_time: 0.000304 Rows_sent: 16313  Rows_examined: 114458

Notes:

• A user must have at least one row in user_translation table.
• I had to use ANY_VALUE() or MAX() to avoid this error: SQL Error (1055): Expression #20 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'trans.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
• The query takes ~0.250 sec. for only 16313 rows, imagine if there are 1,000,000 rows !
• The same query scenario is used for other tables like countries, articles, news, groups, categories.

---

Database Tables

Table: languages

CREATE TABLE `languages` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `lang_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `lang_code` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `timestamp` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `languages_lang_code_index` (`lang_code`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Table: users

CREATE TABLE `users` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `mobile` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
    `email` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `image` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `gender` TINYINT(1) NOT NULL,
    `date_of_birth` DATE NULL DEFAULT NULL,
    `timezone` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `score` BIGINT(20) NOT NULL DEFAULT '0',
    `is_searchable` TINYINT(1) NOT NULL,
    `is_deleted` TINYINT(1) NOT NULL,
    `role` TINYINT(4) NOT NULL,
    `status` TINYINT(4) NOT NULL,
    `uuid` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `country_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `city_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `district_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `users_uuid_unique` (`uuid`) USING BTREE,
    INDEX `users_country_id_foreign` (`country_id`) USING BTREE,
    INDEX `users_city_id_foreign` (`city_id`) USING BTREE,
    INDEX `users_district_id_foreign` (`district_id`) USING BTREE,
    INDEX `tmp_idx_deleted_at` (`deleted_at`) USING BTREE,
    CONSTRAINT `users_city_id_foreign` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT `users_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT `users_district_id_foreign` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON UPDATE RESTRICT ON DELETE SET NULL
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Table: user_translations

CREATE TABLE `user_translations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `second_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `third_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    `user_id` BIGINT(20) UNSIGNED NOT NULL,
    `language_id` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `user_translations_user_id_index` (`user_id`) USING BTREE,
    INDEX `user_translations_language_id_index` (`language_id`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Table: countries

CREATE TABLE `countries` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_general_ci',
    `code` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `wiki_id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `flag` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `is_deleted` INT(11) NOT NULL DEFAULT '0',
    `timestamp` BIGINT(20) NULL DEFAULT '0',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `countries_code_index` (`code`) USING BTREE,
    INDEX `countries_flag_index` (`flag`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;