MySQL: How to optimize this QUERY? Calculating SUM by year(date) -

-

i have query calculating sums of values per every year based on date.

it works, heavy, takes minute 2 minutes running on 10k records

is there way optimize this, or write in more efficient way?

"select departments sum(case when year(employment_date) = '1990' 1 else  0 end) '1990',"     + "sum(case when year(employment_date) = '2010' 1 else  0 end) '2010',"     + "sum(case when year(employment_date) = '2011' 1 else  0 end) '2011',"     + "sum(case when year(employment_date) = '2012' 1 else  0 end) '2012',"     + "sum(case when year(employment_date) = '2013' 1 else  0 end) '2013',"     + "sum(case when year(employment_date) = '2014' 1 else  0 end) '2014',"     + "sum(case when year(employment_date) = '2015' 1 else  0 end) '2015',"      + "sum(case when year(employment_date) = '2016' 1 else  0 end) '2016',"      + " count(departments.dept_id) total "     + "from employees inner join departments on employees.employee_id=departments.employee_id , departments.dept_id = ?";   sample resuts      |departments  | total | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 |     |data systems | 100   | 30   | 10   | 5    | 15   | 20   | 12   | 8    |     |social ssmp  | 70    | 10   | 10   | 15   | 15   | 4    | 6    | 10   |

in mysql, 1 of best way improve query performance indexing.the whole point of having index speed search queries cutting down number of records/rows in table need examined.

create index emp_index on employee (employment_date, employee_id); create index dept_index on departments(departments , dept_id );

please refer link more info.

just quick suggestion.. indexing costs additional writes , storage space, if application requires more insert/update operation, might want use tables without indexes, if requires more data retrieval operations, should go indexed table.


Comments

Post a Comment

Popular posts from this blog

html - Styling progress bar with inline style -

-
i trying set progress bar color red using inline style. need in way because have access <body> part of document. style need apply: progress { color: red } progress::-moz-progress-bar { background: red;} progress::-webkit-progress-value { background: red; } progress[aria-valuenow]:before { background: red; } i tried <progress style="background: red;"> incorrect effect, progress bar color turns blue red background. guide right direction desired style? thank in advance. here live example: https://jsfiddle.net/z36d3f6l/
Read more

java - Oracle Sql developer error: could not install some modules -

-
i have downloaded fresh copy of sqldeveloper ( sqldeveloper-4.1.3.20.78-no-jre ) oracle website. when unzip , lauch gui, keep getting following error message: warning - not install modules: dynamic module config - no module providing capability org.netbeans.netbinox found. my system: windows 7 32 bit hp probook. jdk 1.6 , 1.7, 1.8 available in java/jdk subdirectory. netbeans 8.0.2 installed finally got way out after dirty hours. extracted sqldeveloper zip in nested directory. i.e: e://a/b/c/sqldeveloper. starting sqldeveloper gui above directory threw erros. solution: go in c:\users**username**\appdata\roaming : delete sql developer , sqldeveloper folders extract sql developer zip in new non nested directory. i.e: e:// start , should work now reference: https://community.oracle.com/thread/3870680?start=0&tstart=0
Read more

How to use autoclose brackets in Jupyter notebook? -

-
this may sound silly question, how make use of autoclose brackets in jupyter notebook? example, when type print( jupyter notebook auto-closes brakets print() and places cursor inside. type argument, say print(1 + 1) now cursor between second 1 , right bracket ) . key navigate right of ) ? of course can press end or → or ) achieve this, not seem save time, purpose of brackets autoclose suppose? thanks in advance.
Read more