First time using TiDB, many projects I took over use decimal(20,0) and decimal(30,0) types as primary keys. Does TiDB have special optimizations for these types? I don't understand why these types are used as primary keys?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 第一次使用TiDB,接手的项目很多使用decimal(20,0) decimal(30,0) 这种类型作为主键,请问tidb是对这种类型有特殊优化吗,我不明白为啥要使用这种类型作为主键?

| username: TiDBer_LxReQxvt

This is my first time using TiDB. The project I took over uses types like decimal(20,0) and decimal(30,0) as primary keys in many places. Does TiDB have special optimizations for these types? I don’t understand why this type is used as a primary key. This is very different from my understanding. In my understanding, the decimal type is only used for fields involving small number calculations. Can experts in TiDB help clarify this? Is it a mistake to use this type as the primary key in our project? Can we replace it with varchar(30)?

| username: tidb菜鸟一只 | Original post link

Isn’t decimal the main data type used for amounts? It allows for precise calculations, avoids rounding errors, can define decimal places, and has a large range. Varchar doesn’t seem to fit with it, isn’t varchar purely a string type?

| username: TiDBer_LxReQxvt | Original post link

I know that decimal is mainly used for fields such as monetary calculations. What I’m wondering about is the significance of using decimal as a primary key. Can it be understood that the designer might have considered using integers beyond the range of BIGINT as the primary key with decimal(30,0), and that the performance is higher compared to using string types?

| username: zhanggame1 | Original post link

Numbers generally have significantly better performance than strings.

| username: tidb菜鸟一只 | Original post link

So you mean that each table has a separate meaningless primary key ID, and you chose the decimal(30,0) type for it? Why not use bigint then?

| username: TiDBer_LxReQxvt | Original post link

That’s right, the range of bigint is not that large.

| username: redgame | Original post link

bigint

| username: 我是咖啡哥 | Original post link

TiDB does not have such a recommendation; it always suggests using bigint. This approach is definitely not due to TiDB’s recommendation; you should ask the designer.

| username: tidb菜鸟一只 | Original post link

However, using decimal as the primary key does not create a clustered index table…

| username: ljluestc | Original post link

In TiDB, using decimal(20,0) or decimal(30,0) as primary keys is a valid approach. TiDB indeed has special optimizations for the decimal type to ensure efficient storage and computation.

The decision to use decimal types as primary keys depends on the specific requirements and characteristics of the data in the project. Here are a few points to consider:

Precision and Scale: Decimal types allow precise storage and computation of numbers with specified precision and scale. If your primary key values represent numbers and require a specific level of precision and scale, using decimal types can ensure the accuracy of data representation.

Range of Values: If the primary key values have a wide range and need to cover a large number of decimal digits, decimal types can meet such requirements. They allow you to define precision and scale as needed, providing flexibility in representing various numerical values.

Sorting and Comparison: Decimal types support sorting and comparison operations, which are essential for primary keys. If your primary key values need to be sorted or compared based on their decimal representation, using decimal types is appropriate.

Regarding using varchar(30) as a replacement for decimal types in primary keys, it depends on the nature of the data and how you intend to use the primary key. Varchar types are suitable for certain scenarios, such as when primary key values represent alphanumeric codes or identifiers. However, varchar types have different sorting and comparison behaviors compared to decimal types. It is important to ensure that the chosen data type aligns with the intended use and requirements of the primary key.

To determine whether the primary key type used in the project is correct, it is crucial to consider the specific use case, data characteristics, and performance requirements. Consulting with the original project team familiar with the project design and requirements or TiDB experts may be helpful. They can provide more insights and guidance for your specific scenario.

Overall, the decision to use decimal types as primary keys in TiDB is a valid approach that offers optimizations for efficient storage and computation. However, it is essential to evaluate the project’s requirements and consider alternative data types, such as varchar, based on the specific needs of the primary key values.

| username: zhanggame1 | Original post link

Even if the primary key is varchar, it is still a clustered table, unless you don’t provide a primary key. By default, there is always one.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.