List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:April 5 2006 2:14pm
Subject:Re: very long query for such a simple result
View as plain text  
You must be able to run it as a single call, or as a single TRANSACTION?

What do you mean by "single call"?  One PHP command?  One command on
the commandline?  one script run?

-Sheeri

On 4/4/06, Ed Reed <EReed@stripped> wrote:
> 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