Query SQL Optimization

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

Original topic: 查询SQL优化

| username: TiDBer_E3pRgGAy

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.6.0
[Reproduction Path]
Join query between two large tables is slow
[Encountered Problem: Problem Phenomenon and Impact]



The probe phase is taking too long. Are there any ideas or methods to optimize this?
[Resource Configuration]

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

In the process of selecting 105w from 396w, you’re still not satisfied with 500ms? The data volume is right there.

The optimization method is to find a way to push down the aggregation, first calculate the aggregation result of the u table, and then join it with the small table up.

| username: TiDBer_E3pRgGAy | Original post link

This was extracted from a large query, and the entire large SQL takes more than a second to execute. Analyzing this small part takes up half of the time, so I’m trying to find a way to optimize this part.

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

The one you wrote above should be equivalent to mine, right?

| username: TiDBer_E3pRgGAy | Original post link

However, I still need to use the attributes in table u to join other tables with conditions, so I can’t directly use count.

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

It doesn’t matter for the u table, you can add any conditions as long as they are not based on the up table.

If the conditions are based on the up table, you can handle it like this:

	SELECT
		count(1)
	FROM
		t USER u LEFT JOIN t USER_GROUP up ON u.USER_GROUP_id = up.id 
	WHERE
		u.account_id = ''
		AND u.deleted = 0 
		AND up.xxxx = xxxx

This is much more efficient than selecting 7-8 columns of data that you don’t even look at and then counting them again.

| username: TiDBer_E3pRgGAy | Original post link

Execution time is about the same.

| username: TiDBer_E3pRgGAy | Original post link

The TableReader_41 is still slow, and the data pushed from TiFlash to TiDB is too large.

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

You hit the nail on the head. Just calculate it and then push it up.

| username: Kongdom | Original post link

:thinking: This up table is useless, right?

| username: TiDBer_E3pRgGAy | Original post link

I made some changes, and now it works. It can be done within 100ms.

| username: TiDBer_E3pRgGAy | Original post link

Useful, adding conditions in business.

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

Just great :+1:

| username: redgame | Original post link

Impressive…

| username: 大飞哥online | Original post link

Bit by bit digging, bit by bit analyzing, awesome.

| username: Kongdom | Original post link

:+1: :+1: :+1: Sentence optimization is a long and arduous task~

| username: Kongdom | Original post link

In our actual use, we found that if the business requires association, it is best to use inner join. The efficiency of inner join is much higher than that of left join.