Select the LEFT (pp. Add_time, 4) date,
The sum (case when pp. License_type='5' end then 1 else 0) 'regist5,
The sum (case when pp. License_type='7' end then 1 else 0) 'regist7,
The sum (case when pp. License_type='1002' end then 1 else 0) 'regist1002,
The sum (case when pp. License_type='1201' end then 1 else 0) 'regist1201,
The sum (case when pp. License_type='1202' end then 1 else 0) 'regist1202'
The from t_person_plan pp where pp. Plan_status not,7,9 (6) in the and concat (LEFT (pp. Dept_id, 4), '00')='320200'
And the left (pp. Add_time, 4) & lt; '2017' GROUP BY LEFT (pp. Add_time, 4)
The implementation results as follows:


Ask how the mysql optimization is better? After nearly 3 million data, the data will also increased, thank you.

CodePudding user response:

Don't so much data packet must fast

To extract some larger granularity data in advance into the statistics in statistical table then

CodePudding user response:

Meet the conditions for the amount of data, if less, so you two computation in the where condition, can make computed columns and add index
If large proportion, optimize the room is not big, but also can improve some performance computed columns

CodePudding user response:

refer to the second floor ZJCXC response:

meet the conditions for the amount of data, if less, so you two computation in the where condition, can make computed columns and add index
If large proportion, optimize the room is not big, but some computed columns can also promote performance

Computed columns to ask, how to call? For example, you may write a simple SQL? thank you

CodePudding user response:

reference 1st floor rucypli response:

so much data packet must fast not

To extract some larger granularity data in advance into the statistics in statistical list then statistical

This is a method, I'm thinking of want to write a stored procedure, the statistical data in a table, so a lot