How to do a SELECT from multiple tables of the same type in one query?

Question

MySQL

Good afternoon!
There is a table `table`, it has 500'000'000 records and it weighs 40GB.
The table during the day works on SELECT, very rarely on UPDATE / DELETE and once a day (at night) on INSERT.
Question 1 : Is this table size and number of records critical? After all, a table is a file on a hard drive, and despite all the caching mechanisms, the MySQL engine has to work with a 40GB file.
Question 2 : Considering that having such a large table is bad, I split the table into 10 tables of 50'000'000 entries, with the names `table_0`, ..., `table_9`. Is it possible to create a beautiful query for SELECT / UPDATE / DELETE groups of tables?
The way I know for SELECT is UNION:

(SELECT * FROM `table_0` WHERE `id` IN (1, 2, 3)) 
UNION ALL (SELECT * FROM `table_1` WHERE `id` IN (1, 2, 3))
...
UNION ALL (SELECT * FROM `table_9` WHERE `id` IN (1, 2, 3))

And for UPDATE / DELETE, you will have to do 1 query for each table.
Something like this would be ideal

SELECT * FROM `table_0`, `table_1`, ..., `table_9` WHERE `id` IN (1, 2, 3) 
UPDATE `table_0`, `table_1`, ..., `table_9` SET `field` = 10 WHERE `id` IN (1, 2, 3)

Question 3 : How efficient is splitting a table into sub-tables? After all, in fact, MySQL still has to scan 40GB, but not in one file, but in 10 files?
Thanks for the replies and advice!

Answer 1

Dividing a table into parts within the same server is completely pointless and even harmful.
If the DBMS is configured correctly (indexes are assigned), when executing queries, it looks into the index, and does not shovel the entire file.

Answer 2

Use partitions, then you will not have such problems. However, keep in mind that MySQL has problems with cascading constraints on large partition tables.
dev.mysql.com/doc/refman/5.1/en/partitioning.html

Answer 3

advice to read