If you query all the data after the horizontal sub-table

horizontal subtable can improve the efficiency of the query, but if the order table is divided into 100 tables, I want to query all the orders of a user, how to deal with this?

Jul.15,2022

order1....order100

if the user uid=6 uid%100=6, then it exists in the order6 table
if the user uid=300 uid%100=0 exists in the order100 table
use the user uid and the remainder to query the order table corresponding to the remainder to get all the order data of the user


union connect


use union all connection, do not use union, otherwise there will be a comparative de-duplication process, which is not needed in your case.
reference: official documents


union


I don't know if you use data sharding middleware, which provides routing and data aggregation functions without you changing the code.


is not clear about your specific business scenario. If there are many queries about user data in your business scenario, then the strategy of sub-table should be related to users. Try to ensure that each user's data is in the same table.
if the order ID is divided into 100 tables, it is not good to simply use union all. Union the query results of these 100 tables every time union. This operation is both troublesome and not elegant to mysql.
under the premise that the split table policy cannot be modified, the MyISAM merge engine is recommended. The relevant knowledge of MyISAM merge can be self-google.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7b6412-15664.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7b6412-15664.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?