Select C, count (*)
From the table
Where A=1
And B & gt; 1
Group by C

This is a statistical statements, I built a portfolio on the table index AB, but this will result in a temporary table sorting,
If it set up AC scanning more rows,
For a great god, and how to design better?

CodePudding user response:

ABC index

idx_a_b_c

CodePudding user response:

reference 1st floor p270228163 response:

ABC index

Idx_a_b_c

is not a, b, c, b is range queries, will snap joint index, ABC is equivalent to AB

CodePudding user response:

refer to the second floor qq_37247448 response:

Quote: refer to 1st floor p270228163 response:

ABC index

Idx_a_b_c

is not a, b, c, b is range queries, will snap joint index, ABC is equivalent to AB

Can use the

CodePudding user response:

According to the six uid=34 query record
Then found in the six char12 & gt; All records 20

Then group by query

Even if is above, first & gt; And then=, but also according to the query

CodePudding user response:

If not sure, after the index and use the explain to analyse the SQL statement is know to have hit the index,

CodePudding user response:

reference p270228163 reply: 3/f

Quote: refer to the second floor qq_37247448 response:

Quote: refer to 1st floor p270228163 response:

ABC index

Idx_a_b_c

is not a, b, c, b is range queries, will snap joint index, ABC is equivalent to AB

Can use the

although used to, but there is also a temporary table and filesort, extra that column, so I don't in the temporary table and index between, both can only choose one

CodePudding user response:

https://blog.csdn.net/szwangdf/article/details/80789279
This is both an example, 2 table group by optimization, you is a single table,

Where A=1 and B & gt; 1
, and then to group by, this will create a temporary table is inevitable, otherwise how grouping statistics?

CodePudding user response:

Indexed column order, C, A, B