Help to improve MySQL query

A database was designed with the following tables:

Incidents
_________
id (auto incremented int)
....

Calls
_____
id (auto incremented int)
incidentId (foreign key)
status (varchar 32)
....

The status of the last Call is the status of the related Incident.
Statuses can be "Not started" through various states up to "Completed".
The status column never has the text "Open".
If the last Call for the related Incident is not "Completed", then it
is considered to be "Open".

My task is to getIncidentsWithStatus("Open") using PHP.

The existing inefficient method is in the PHP function
getIncidentsWithStatus($status = "Open"), made worse by mingling with
PHP and then another MySQL query. It first finds
$theHugeListOfIncidentIds of the last Calls OF ALL INCIDENTS, then
uses Calls.id IN ($theHugeListOfIncidentIds) AND Calls.status NOT LIKE
'Completed'. The reason this was done was that if Calls.status NOT
LIKE 'Completed' was used first, then the result would include all
Incidents.

A) What would be an efficient MySQL query with the database in the
present state to getIncidentsWithStatus("Open")?

I can think of two alternatives, which require the database to be modified:
1a) Add a trigger to update a new column named "statusFromCall" in the
Incidents table when the Calls.status is updated.
1b) Add PHP code to update the new column named "statusFromCall" in
the Incidents table when the Calls.status is updated.
2) Then just query for Incidents WHERE statusFromCall NOT LIKE 'Completed'.

B) What would be the MySQL query to create such a trigger in 1a?

Thanks.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help to improve MySQL query

How about "select Incidents.* from Incidents inner join Calls on
Incidents.id=Calls.incidentid where Calls.status='Open'"?

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Dee Ayy wrote:
> A database was designed with the following tables:
>
> Incidents
> _________
> id (auto incremented int)
> ...
>
> Calls
> _____
> id (auto incremented int)
> incidentId (foreign key)
> status (varchar 32)
> ...
>
> The status of the last Call is the status of the related Incident.
> Statuses can be "Not started" through various states up to "Completed".
> The status column never has the text "Open".
> If the last Call for the related Incident is not "Completed", then it
> is considered to be "Open".
>
> My task is to getIncidentsWithStatus("Open") using PHP.
>
> The existing inefficient method is in the PHP function
> getIncidentsWithStatus($status = "Open"), made worse by mingling with
> PHP and then another MySQL query. It first finds
> $theHugeListOfIncidentIds of the last Calls OF ALL INCIDENTS, then
> uses Calls.id IN ($theHugeListOfIncidentIds) AND Calls.status NOT LIKE
> 'Completed'. The reason this was done was that if Calls.status NOT
> LIKE 'Completed' was used first, then the result would include all
> Incidents.
>
> A) What would be an efficient MySQL query with the database in the
> present state to getIncidentsWithStatus("Open")?
>
> I can think of two alternatives, which require the database to be modified:
> 1a) Add a trigger to update a new column named "statusFromCall" in the
> Incidents table when the Calls.status is updated.
> 1b) Add PHP code to update the new column named "statusFromCall" in
> the Incidents table when the Calls.status is updated.
> 2) Then just query for Incidents WHERE statusFromCall NOT LIKE 'Completed'.
>
> B) What would be the MySQL query to create such a trigger in 1a?
>
> Thanks.
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help to improve MySQL query

Hi

You do not say how you identify the last call (there is no date/time
field for example), so a complete answer is not really possible

Do not use "NOT LIKE 'Completed'", it's an inefficient way of doing "!=
'Completed'"
--
Niel Archer



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help to improve MySQL query

On Fri, Aug 8, 2008 at 5:25 PM, Micah Gersten wrote:
> How about "select Incidents.* from Incidents inner join Calls on
> Incidents.id=Calls.incidentid where Calls.status='Open'"?
....
>
> Dee Ayy wrote:
....
>> The status column never has the text "Open".
....

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help to improve MySQL query

Use an appropriate status.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Dee Ayy wrote:
> On Fri, Aug 8, 2008 at 5:25 PM, Micah Gersten wrote:
>
>> How about "select Incidents.* from Incidents inner join Calls on
>> Incidents.id=Calls.incidentid where Calls.status='Open'"?
>>
> ...
>
>> Dee Ayy wrote:
>>
> ...
>
>>> The status column never has the text "Open".
>>>
> ...
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Help to improve MySQL query

On Sat, Aug 9, 2008 at 1:32 AM, Niel Archer wrote:
> Hi
>
> You do not say how you identify the last call (there is no date/time
> field for example), so a complete answer is not really possible

With the "id (auto incremented int)", the last record of either table
would be the largest id in a particular group.


> Do not use "NOT LIKE 'Completed'", it's an inefficient way of doing "!=
> 'Completed'"

I'll modify that as you said.

My real concern is that $theHugeListOfIncidentIds keeps growing and is
used in the "Calls.id IN ($theHugeListOfIncidentIds)".

Even if the $theHugeListOfIncidentIds was replaced with an actual
MySQL query instead of first being processed by PHP, I think that is a
bad approach (but maybe that is the solution if I only change this
query and do not change/add columns or states).

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php