How to store translates in MySQL to use join?-CodePudding

I have a table that contains all translations of words:

CREATE TABLE `localtexts` (
  `Id` int(11) NOT NULL,
  `Lang` char(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'pe',
  `Text` varchar(300) DEFAULT NULL,
  `ShortText` varchar(100) NOT NULL,
  `DbVersion` timestamp NOT NULL DEFAULT current_timestamp(),
  `Status` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As example there is a table that refers to localtexts:

CREATE TABLE `composes` (
  `Status` int(11) NOT NULL DEFAULT 1,
  `Id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table above has foreign key Id to localtexts.Id. And when I need to get word on English I do:

SELECT localtexts.text, 
       composes.status 
FROM composes 
LEFT JOIN localtexts ON composes.Id = localtexts.Id
WHERE localtexts.Lang = 'en'.

I'm concerned in performance this decision when there are a lot of tables for join with localtexts.

CodePudding user response：

You might find that adding the following index to the localtexts table would speed up the query:

CREATE INDEX idx ON localtexts (Lang, id, text);

This index covers the WHERE clause, join, and SELECT.