Is There Still Room for Optimization?

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

Original topic: 还有优化的空间吗?

| username: TiDBer_7Q5CQdQd

Three 12-core 48GB databases. This SQL query takes 10 seconds. Is there still room for optimization?

| username: zhanggame1 | Original post link

Optimization can only be done at the business layer. With such a large amount of data, there’s no WHERE condition to filter out some data.

| username: TiDBer_7Q5CQdQd | Original post link

What is the recommended configuration for my data?

| username: zhanggame1 | Original post link

How many TiFlash instances have you deployed? You might consider adding more and deploying them independently.

| username: TiDBer_7Q5CQdQd | Original post link

TiFlash and other components share the same node.

| username: Jellybean | Original post link

Scanning 220 million and 280 million rows from two tables respectively for a join operation, completing within 10 seconds is not considered slow.

Looking at the execution plan, it seems that TiFlash’s MPP acceleration has already been utilized, which should be an optimal result.

If you still want to optimize further, you can check where the bottleneck is:

  • Use explain analyze to see the specific execution process.
  • Check the Dashboard to find this slow SQL (usually the threshold is 3 seconds), and look at the execution operators to confirm which part is relatively slow (recommended).

Then optimize based on the bottleneck.

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

I think apart from upgrading the machine configuration, there’s not much else that can be done.

| username: TiDBer_vfJBUcxl | Original post link

Adding machines is the most effective solution.

| username: 大飞哥online | Original post link

  1. Start at the source, add the WHERE condition first.
  2. Then expand TiFlash.
| username: Kongdom | Original post link

Would rewriting it using “in” or “exists” be faster?

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

A table with 220 million rows and a table with 28 million rows, doing a full join, and you think 10 seconds is slow?

Why don’t you directly state your requirements, how many seconds do you think is appropriate? Or are there any other conditions that haven’t been mentioned, like how much time can be reduced based on certain conditions?

| username: Hacker007 | Original post link

Try not using TiFlash, then add an index and give it a try.

| username: 像风一样的男子 | Original post link

Optimize it from a business perspective. There’s no good solution for such large tables.

| username: system | Original post link

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