List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 4 2006 9:35pm
Subject:Re: very long query for such a simple result
View as plain text  
Ed,

>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 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: ProblemReports
> Fields: PRNo, Status, Priority, Responsible
>  
> Table: Employees
> Fields: 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
>   

Attachment: [text/html]
No virus found in this outgoing 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