Simply adding an index would help immensely.Īddin an index would speed up the operation then there would be up to 10-100 disk reads to get a resultset. What you have: Well you don't even have an index on object_id, so it needs to scan the table. This allows multiple clients insert rows without worry about dups.Īll 3 cases will improve performance because the 10-100 rows can be fetched more efficiently. (BIGINT is 8 bites, a lot smaller than what you seem to have now.) Note that AUTO_INCREMENT lets you insert rows without specifying id a number will be generated for you. Change to PRIMARY KEY(object_id, client_id)Ĭase 2: id is needed to provide uniqueness:Ĭhange the PK to these two: PRIMARY KEY(object_id, id),Ĭase 3: If id is needed, but it does not need to be a UUID:, change to BIGINT AUTO_INCREMENT and do as in case 2.WHERE object_id = '.', then having the PRIMARY KEY start with object_id would help performance the most.Ĭase 1: The combination of object_id and client_id is unique: My blob on UUIDs is somewhat irrelevant since you have your own UUIDs. In Future we might perform join as well but as of now no join on this table. There is no such performance issue we are facing because of the partition but its one big table doing and DDL is very difficult and later of we plan to migrate to some other version or data store it will be even problematic. ID is not for joining the table its for look up as well. We also query based on CLIENT_ID like where CLIENT_ID='6tgbcrqskjfhsd78o82mcrzz' but 90 % or 95% of the time we search based on OBJECT_ID like OBJECT_ID='6tgbcrq9pkjfnezsdo82mcrzz' Most common access pattern is on OBJECT_ID ,But if we create partition then select might have to go 700 millions partition not sure need to know why it is advised to create partition on OBJECT_ID int this case ? We have already Index on Primary key default and CLIENT_ID and OBJECT_ID. We are on MySQL 5.7 but can migrate to 8 if that would give us some real benifit. UUID example (6tgbcrq9pkjfnezsdo82mcrzz) is not Mysql generated id its generated by our application So the 90 % of the time we look up by OBJECT_ID a key search and that return 99% of the time 10 to 100 values. We have limitation with MySQL partition that partition column should be part of primary key but in our case we don't have ,How can we over come that problem ? Will clustered index in this case ? Instead of native partition can we create different small tables which will be better ? My UUID is in sorted manner so is there anyway partition can be created We are thinking to partition based on client so that because of few big client other small client should not get impacted. OBJECT_ID (700 millions case but 90 % of the select happen based on this ) CLIENT_ID (10K distinct clients and query will never happen across the client )ī. On which column we should partition this table ?Ī. I have very specific question about this. I am new to data base and we don't have any DBA expert in our team who can help is with this. We want to partition this table as this is single table and will grow very fast in future. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin `OBJECT_ID` varchar(255) DEFAULT NULL, UUID `CLIENT_ID` varchar(255) DEFAULT NULL, UUID I have a table which has 3 Billions rows and 6 TB data in MySQL Aurora and this is expected to grow very rapidly in future 50% increase in a year.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |