MySQL Optimization Program

Basically solved by index.
Usually the index key is in where, group by, and order by related columns
Only one index can be used for a table (when querying)
So it's best to use a federated index when you want to execute a complex query, which is index (A,b,c,d,....)
Where to pay attention to things,
The index is left-to-right, and if it is index (A,B,C) then you can
1. Where a = x and B = y and C = Z
2. Where a = x order by b,c
3 Where a = x GROUP by a,b,c
What group by needs to be aware of:
If it is a multi-table query, the group by index can only be the last column of the join table
Like a straight join B
Then group by a.x is possible.
If it's group by A.X,B.Y, then it's fair.
Table Join finished there can be only one table index, and the indexes of the other table's columns are all obsolete.
But their index is helpful at the time of the join.
What to pay attention to in order by
It is difficult to order by after group by, because they have to go along with a federated index. But it's not that good.
And if you order by has DESC to go back also will not go index.

So, there are cases where there's no way to optimize with indexes.
Both group by and order by are very hurtful, especially if you join table, it will usually have a using temp filesort.
So try to satisfy one of them.
So if it's really not enough, then make sure the amount of data is not too big or GG.

Then some of the other directions are to optimize as much as possible with table design
For example: Inverse paradigm
is to put multiple table data in a table so that he doesn't have to join.
This is the drawback is the repeated data many pits, admin update when you want to synchronize the place also more
It will be hard to maintain up.
The advantage is that data is easily built on a table index.

Pre-calculation
is not to take the data when the calculation, as far as possible beforehand put in.
Then you can reduce the need for group by sum order by sum.

Cache
To ensure that each statement can be cached, do not use date functions to write statements, such as WHERE a.x = Now ()

Take the sentence apart
Let C # make several queries and simplify the SQL statement.
The advantage is that you can distribute the cache and easily use the indexes of each table.

Of course, in C #, it's going to be LINQ.