Index Performance Issues

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

Original topic: 索引性能问题

| username: TiDBer_tfjyrugx

In these distributed database implementations with global indexes, even with just one index, performance drops to below 30%, and with eight indexes, performance generally drops to below 10%. In contrast, single-node databases like MySQL maintain performance above 85% even with eight indexes. This corroborates the viewpoint we mentioned earlier: “Compared to single-node transactions, distributed transactions still have an insurmountable gap in terms of cost (or performance), with this gap being at least threefold.” Is the write performance really that poor? Can it be resolved?

| username: zhanggame1 | Original post link

Specifically, you can test it. The impact of secondary indexes on write performance is not as exaggerated as some articles suggest. In my tests, the performance dropped by at most 50%.

| username: zhanggame1 | Original post link

Additionally, distributed databases can improve performance by adding more machines, whereas single-node databases cannot handle the load once they reach their limit.

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

If you have a small table with less than 10 million rows, MySQL is definitely better in terms of cost (or performance). But if you have a large table with more than 1 billion rows, can you still use MySQL?

| username: zhaokede | Original post link

It’s not that exaggerated. You can test by writing fixed data in business scenarios with and without indexes on the same table.

| username: xfworld | Original post link

What service to use in what scenario, there’s no one-size-fits-all solution…

Fixed scenarios and problem points are more important…

If all the problems can be solved with MySQL, why use anything else?

| username: TiDBer_vfJBUcxl | Original post link

In the implemented global index, even with only one index, performance drops below 30%. With eight indexes, performance generally falls below 10%. In contrast, for a single-machine database like MySQL, performance remains above 85% with eight indexes. This confirms our previously mentioned viewpoint: “Compared to single-machine transactions, distributed transactions still have an insurmountable gap in terms of cost (or performance), with this gap being at least threefold.” Is the write performance really this poor? Can it be resolved?

Does performance have to drop this much?

| username: 有猫万事足 | Original post link

I feel it’s better to test it myself.
I’m not sure how you view this text, but to me, it has a bit of a ‘shock department’ vibe.
If you have doubts about the performance, why not test it yourself? The conclusions you get that way will be more meaningful.