List:General Discussion« Previous MessageNext Message »
From:Ed Reed Date:April 4 2006 9:53pm
Subject:Re: very long query for such a simple result
View as plain text  
Thanks for the quick reply Peter. Unfortunately, this is all legacy stuff that I have to
make work together and the problemreport table does not reference the employeeid in the
employees table. It was all create about ten years ago and the data has just always been
migrated to the db du jour. I'm currently using MySQL 4.1x but most importantly I must be
able to run the entire query in a single call.
 
Thanks for the help.

>>> Peter Brawley <peter.brawley@stripped> 4/4/06 2:35:49 PM
> >>>

Ed,

The big slowdown in your query is likely the join on 

  ProblemReports.Responsible = CONCAT(Employees.FirstName, ' ',Employees.LastName)) 

Eek. Does the employees table not have a primary key, and does the problemreports table
not reference that key? If not,  I would think a first priority would be to fix that.

Meanwhile, you can simplify your monster query by writing the user's problem priorities to
a temp table, then doing a simple pivot on priority to generate your sentence. Something
like ...

DROP TEMPORARY TABLE IF EXISTS ProbSum;
CREATE TEMPORARY TABLE ProbSum
SELECT Priority 
FROM ProblemReports, Employees 
WHERE ProblemReports.Status='Open' 
AND ProblemReports.Responsible=CONCAT(Employees.FirstName, ' ',Employees.LastName) 
AND Employees.DateTerminated IS NULL 
AND Employees.UserName='User1'

SELECT COUNT(*) INTO @n FROM ProbSum;

SELECT 
  IF( @n = 0, 
      '',
      CONCAT( 'You have',
              @n,
              'Problem Report',
              IF(@n=1,'','s'),
              ': Priorities(High=',
              SUM(IF(Priority='High',1,0)),
              ',Med=',
              SUM(IF(Priority='Med',1,0)),
              ',Low=',
              SUM(IF(Priority='Low' ,1,0)),
              ')'
            )
    )
FROM probsum;

DROP TEMPORARY TABLE probsum;

All this would be easier in a stored procedure, if you have MySql 5.

PB   

-----

Ed Reed wrote: 
Can someone help me simplify this query please? It's meant to return a single string
result that looks something like this, "You have 12 open Problem Reports:
Priorities(High=5, Med=6, Low=1)" The relavent columns from the two tables are  Table:
ProblemReportsFields: PRNo, Status, Priority, Responsible Table: EmployeesFields:
Firstname, Lastname, DateTerminated, UserName Here's the query Select IF((@a:=(SELECT
Count(PRNo) From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1')))>0,  If (@a=1,
ConCat('You have one open Problem Report: Prioritiy = ', (Select Priority From
ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1'))),  Concat('You have
', @a, ' open Problem Reports: Priorities(',  (Select ConCat('High=',Count(Priority))
From ProblemReports, Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='High')),', ',(Select ConCat('Med=',Count(Priority)) From ProblemReports,
Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Med')),', ',(Select ConCat('Low=',Count(Priority)) From ProblemReports,
Employees WHERE (ProblemReports.Status='Open') AND
(ProblemReports.Responsible=ConCat(Employees.FirstName, ' ',Employees.LastName)) AND
(Employees.DateTerminated Is Null) AND (Employees.UserName='User1') And
(Priority='Low')),')')),'');  No virus found in this incoming message.Checked by AVG Free
Edition.Version: 7.1.385 / Virus Database: 268.3.5/300 - Release Date: 4/3/2006  

Thread
very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultSGreen4 Apr
  • Re: very long query for such a simple resultPeter Brawley4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
Re: very long query for such a simple resultEd Reed4 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
  • Re: very long query for such a simple resultsheeri kritzer5 Apr
Re: very long query for such a simple resultEd Reed5 Apr
  • Re: very long query for such a simple resultPeter Brawley5 Apr
Re: very long query for such a simple resultEd Reed6 Apr
  • Re: very long query for such a simple resultBarry6 Apr
  • Re: very long query for such a simple resultPeter Brawley6 Apr