How to handle a foreign key when splitting

Question

I am working on fleet management. I have a large number of entries in a location table with the following columns.

• date
• Time
• vehicle number.
• a long
• latitude
• speed
• userid (which is a foreign key ...)

Here, this table will have a write operation every 3 seconds. Therefore, it will have millions of records. So in order to get faster data, I PLAN. Now my question is: -

• How to handle a foreign key? I heard that the section does not support foreign key
• Which column should be used for the section.
• You must have a unique key as a section column.

There will be trillions of records

@ rc-Thanks, man..what performance abt ... see that I insert data every 3 seconds, so I have to run a validation procedure every time I insert data ... so what about performance?

2> I would like to go to the section column as the vehicle is not ..... is there an alternative way ...

Answer 1

Read: MySQL Partition Restrictions

1.) FKs are not supported in partitioned tables.

• One option is to create a stored procedure that inserts / updates the record and checks inside the procedure that the passed user ID is present in your user table before insertion. You must configure permissions for the table so that only SP is allowed to update and paste in order to allow applications and / or users to check. You also need to take precautions when deleting users from the user table.

2.) Which column you use for partitioning will depend on how you access the table. If your queries are always based on the vechicle no value, then it probably makes sense to make a hash section in this column. If you request or report more about something like “which vehicles were added this month” or want to “flip” sections as they become a certain age, then dividing by date may be the way to go. This is what you will need to decide based on your use.

3.) For more information, see the link above.

Edit based on user question:

Inserting a record every 3 seconds is not a big bandwidth. Make sure you have the primary key in your user table so that validation within the procedure is effective. (This is true even if FK support was supported). The database would perform this check behind the scenes if you had FK support, so in that sense it didn't bother you. If the check ends with a bottleneck, you may feel the need to drop it and possibly report user identifier errors as a nightly batch process, but if you are a user table relatively small and correctly indexed, I don’t see that this is a problem.

Another option would be to do the markup manually (i.e. scalding) using partitioned or non-segmented tables. Of course, with non-segmented tables, you can use your own foreign keys. For example, you would divide the table of vehicles into several tables, for example: (if you want to use the vehicleNo as a "key")

VehiclesNosLessThan1000

VehiclesNosLessThan2000

VehiclesNosLessThan ...

VehiclesNosLessThanMAX

Here, you probably want to get the SP again so that the application / user does not know about the tables. The SP will be responsible for inserting / updating the correct table based on the vehicle that is not transferred. You will also need an SP to select data so that the application / user does not know which table to select. For easy access to all data, you can create a view that joins all tables together.

Note that one of the advantages of this is that MyISAM currently locks the entire partitioned table during updates, and not just the partition that it updates. Thus, closing the table facilitates this discussion, since the tables themselves are “partitions”.

Based on the limited data that I have on what you are doing, I will probably write 2 stored procedures, 1 to select data and 1 to update / insert data and use your application for access. Then I would try to perform regular hash splitting on the vehicle first by entering the user_id key in the procedure. If this becomes a problem, you can easily transfer data to several tables without changing the application, because all the logic on how to retrieve and update data is contained in the SP.