In Progress

MySql complex query

I have this query: SELECT DISTINCT Student.Last_Name as lname, Student.First_Name as fname,

Student_Service_Header.header_id as hid, Student_Service_Header.pract_id as pid,

upd, Student_Service_Header.medicaid_id as medid,

Student_Service_Header.service_area as svcarea, sub,

Student_Service_Header.district_id as did,

Practitioner.first_name as practfn, Practitioner.last_name as practln,

[url removed, login to view] as acct, transdt,

Student_Service_Header.QC_status as sts, Student_Service_Header.QC_comment as cmt,

District.district_name as dname

FROM Student

JOIN Student_Service_Header ON Student.medicaid_id = Student_Service_Header.medicaid_id and Student.district_id = Student_Service_Header.district_id

JOIN vw_Service_Details ON Student_Service_Header.header_id = vw_Service_Details.header_id

JOIN Practitioner ON Student_Service_Header.pract_id = Practitioner.pract_id

JOIN District ON Student_Service_Header.district_id = District.district_id

WHERE [url removed, login to view] = (select account from Practitioner where pract_id = '$pract_id') $sqlqual and

(minDOS is null OR (minDOS < (select end_date from School_Year where year_id = '$year') AND minDOS >= (select start_date from School_Year where year_id = '$year')) OR (maxDOS < (select end_date from School_Year where year_id = '$year') AND maxDOS >= (select start_date from School_Year where year_id = '$year')))

order by sub, lname, fname, hid desc

As the database has grown, it has become exceeding slow. I have discovered that one of the views I am using runs in 4 seconds when I just run the query, but the view that runs the same query is taking 11 seconds. I would like to incorporate the functionality from the view into the query, and am looking for help. The vs_Service_Details does this:

select `vw_Details`.`header_id` AS `header_id`,max(`vw_Details`.`lastupd`) AS `upd`,max(`vw_Details`.`transferdt`) AS `transdt`,max(`vw_Details`.`submit`) AS `sub`,max(`vw_Details`.`DOS`) AS `maxDOS`,min(`vw_Details`.`DOS`) AS `minDOS` from `vw_Details` group by `vw_Details`.`header_id`

And, the vw_Details does this:

select `Student_Service`.`header_id` AS `header_id`,str_to_date(`Student_Service`.`date_of_service`,_utf8'%m/%d/%Y') AS `DOS`,date_format(`Student_Service`.`last_update`,_utf8'%m/%d/%Y') AS `lastupd`,`Student_Service`.`transfer_date` AS `transferdt`,`Student_Service`.`submit_yn` AS `submit` from `Student_Service` union select `Student_Service_PCAR`.`header_id` AS `header_id`,str_to_date(`Student_Service_PCAR`.`date_of_service`,_utf8'%m/%d/%Y') AS `DOS`,date_format(`Student_Service_PCAR`.`last_update`,_utf8'%m/%d/%Y') AS `lastupd`,`Student_Service_PCAR`.`transfer_date` AS `transferdt`,`Student_Service_PCAR`.`submit_yn` AS `submit` from `Student_Service_PCAR`

I need this to be optimized as a single query to run as quickly as possible. Thanks!

Skills: MySQL

See more: n query, null, desc, using complex, help mysql, query query, mysql query slow, order taking database, mysql slow, mysql join, order mysql, cmt views, District, practitioner, mysql help, mysql account, mysql views, view mysql, need null, mysql student database, complex mysql query, database student mysql, using sts, upd, select select mysql

About the Employer:
( 4 reviews ) Austin, United States

Project ID: #1088531