Selection from multiple SQL tables of the same structure for one entity

Question

MySQL

I'll start a little from afar. Suppose we have a table (let's say mySQL) that looks like this: The table stores data for an object with ID = `id` in several languages, so PRIMARY KEY = (id, lang). A typical use case for this table: you need to get data data for an object with id = 1, preferably in language 1, if it does not exist, in language 2, otherwise I don’t care which one, if only it was.

CREATE TABLE `table` (
`id` int(11) unsigned NOT NULL,
`lang` tinyint(3) unsigned NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`id`,`lang`)
) ENGINE=InnoDB



Such a task is solved quite simply: The selection is fast enough and using only the PRIMARY key and sorting. Question number 1: how to make such a selection not for one, but for N objects at once, without resorting to subquery: Now imagine the situation that there are 1,000,000 of these objects, plus 20 more languages ​​for each of them. We take and break one table into several with exactly the same structures. Objects are distributed across tables using a primitive hash function of the form (id % N == 0). Question number 2: The task is still the same. How to get such a sample now? I think that the answer to this question will follow directly from question number 1 :) Additional questions, which, probably, are even “more important” than those mentioned earlier:

SELECT * FROM `table` WHERE `id` = 1
ORDER BY
CASE WHEN `lang` = 1 THEN 1
WHEN `lang` = 2 THEN 2
ELSE 3
END ASC
LIMIT 1





SELECT (подзапрос для одного объекта) FROM (запрос, выбирающий ID объектов)




How generally optimal is this way of solving the problem? Maybe there are more elegant solutions?

Something tells me that there must be more elegant ways to solve such a problem :/

Thanks in advance to everyone for the suggested options and discussions! ;)

Answer 1

It is necessary to understand how these same N objects are specified.
If nothing can be said about the nature of the ID selection for these objects, then for question 1, apart from the proposed variant with a subquery, it is hardly possible to think of anything.
If I understand the task correctly, the result of the first question will look something like this: As for the second one... If there are such requests, maybe it's better to split them not by id, but by language?
select *
from `table`
where id in (
    <подзапрос или список для выбора id>
)
order by
    case when lang = 1 then 1
        when lang = 2 then 2
        else 3
    end asc