mysql query takes too much time to run

--001636426b550a9f34047d0b5ca4
Content-Type: text/plain; charset=ISO-8859-1

hi

can anybody help me to check this query is written in good way

select ATX_Status.Server as `Server`, ATX_Status.Load as
`Load`,ATX_Status.System as `System`,
atx.ATX_Status.Timestamp as `TimeStamp`, `Coverage`,
atx.ATX_Summary.Testware as `View`, `Traffic`,
ATX_Status.Status as `Status`, ATX_Request.Requestor as `Requestor`,
atx.ATX_Summary.
Logs as `Logs`, `Priority`, `Server_IP_Address`, atx.ATX_Request.isHostBuild
as `isHost`,
atx.ATX_WebLogs.Logs as `WLogs`, `TestRun`, `PRequestor`, `Reason`,
`AbortRqstr`,
DATE_SUB( str_to_date(ATX_Status.Timestamp, '%m%d%Y-%H'), INTERVAL
ATX_Time.TimeDiff HOUR_MINUTE)
as `ConvTime`, ATX_Reruns.isRerun as rerun, ATX_Reruns.comment as
reruncomment, atx.ATX_RuntimeStats.
lbtStartTime, atx.ATX_RuntimeStats.runStartTime,
atx.ATX_RuntimeStats.totalRunDuration
from atx.ATX_Status
left join atx.ATX_Request on atx.ATX_Request.Timestamp =
atx.ATX_Status.Timestamp
left join atx.ATX_WebLogs on atx.ATX_Status.Timestamp =
atx.ATX_WebLogs.Timestamp
left join atx.ATX_Server on atx.ATX_Status.Server =
atx.ATX_Server.Server_Name
left join atx.ATX_Time on atx.ATX_Server.Server_Location =
atx.ATX_Time.Server_Location
left join atx.ATX_RuntimeStats on atx.ATX_Status.Timestamp =
atx.ATX_RuntimeStats.Timestamp
left join atx.ATX_AbortedJobs on atx.ATX_Status.Timestamp =
atx.ATX_AbortedJobs.Timestamp
left join atx.ATX_Reruns on atx.ATX_Status.Timestamp =
atx.ATX_Reruns.Timestamp
left join atx.ATX_Summary on atx.ATX_Summary.Timestamp =
atx.ATX_Status.Timestamp
where (
ATX_Status.Status like '%running%' or ATX_Status.Status like '%queued%' or
ATX_Status.Status like '%migrating%' or ATX_Status.Status like '%loading%'
or
ATX_Status.Status like '%configuring%' or ATX_Status.Status like
'%activating%' )
and ATX_Server.Server_Status=1 and ATX_Status.isAlive=1
and ATX_Status.Server = 'ALIL-CHN RLAB'
and ATX_Status.System = 'ETSI-NFXSA-NANTA-ISAM-01'
and ATX_Status.Load = 'SD_4003.418' and str_to_date(ATX_Status.Timestamp,
'%m%d%Y')

thanks in advance

Cheers
Faizal S
GSM : 9840118673
Blog: http://oradbapro.blogspot.com

--001636426b550a9f34047d0b5ca4--

Re: mysql query takes too much time to run

--0016e6407d7ca224d0047d0b88aa
Content-Type: text/plain; charset=ISO-8859-1

Sac,
this is like saying I have a pain, what can it be?
Attach more info if you hope in some help, at least the explain plan.

Cheers

Claudio


2010/1/13 F.A.I.Z.A.L

> hi
>
> can anybody help me to check this query is written in good way
>
> select ATX_Status.Server as `Server`, ATX_Status.Load as
> `Load`,ATX_Status.System as `System`,
> atx.ATX_Status.Timestamp as `TimeStamp`, `Coverage`,
> atx.ATX_Summary.Testware as `View`, `Traffic`,
> ATX_Status.Status as `Status`, ATX_Request.Requestor as `Requestor`,
> atx.ATX_Summary.
> Logs as `Logs`, `Priority`, `Server_IP_Address`,
> atx.ATX_Request.isHostBuild
> as `isHost`,
> atx.ATX_WebLogs.Logs as `WLogs`, `TestRun`, `PRequestor`, `Reason`,
> `AbortRqstr`,
> DATE_SUB( str_to_date(ATX_Status.Timestamp, '%m%d%Y-%H'), INTERVAL
> ATX_Time.TimeDiff HOUR_MINUTE)
> as `ConvTime`, ATX_Reruns.isRerun as rerun, ATX_Reruns.comment as
> reruncomment, atx.ATX_RuntimeStats.
> lbtStartTime, atx.ATX_RuntimeStats.runStartTime,
> atx.ATX_RuntimeStats.totalRunDuration
> from atx.ATX_Status
> left join atx.ATX_Request on atx.ATX_Request.Timestamp =
> atx.ATX_Status.Timestamp
> left join atx.ATX_WebLogs on atx.ATX_Status.Timestamp =
> atx.ATX_WebLogs.Timestamp
> left join atx.ATX_Server on atx.ATX_Status.Server =
> atx.ATX_Server.Server_Name
> left join atx.ATX_Time on atx.ATX_Server.Server_Location =
> atx.ATX_Time.Server_Location
> left join atx.ATX_RuntimeStats on atx.ATX_Status.Timestamp =
> atx.ATX_RuntimeStats.Timestamp
> left join atx.ATX_AbortedJobs on atx.ATX_Status.Timestamp =
> atx.ATX_AbortedJobs.Timestamp
> left join atx.ATX_Reruns on atx.ATX_Status.Timestamp =
> atx.ATX_Reruns.Timestamp
> left join atx.ATX_Summary on atx.ATX_Summary.Timestamp =
> atx.ATX_Status.Timestamp
> where (
> ATX_Status.Status like '%running%' or ATX_Status.Status like '%queued%' or
> ATX_Status.Status like '%migrating%' or ATX_Status.Status like '%loading%'
> or
> ATX_Status.Status like '%configuring%' or ATX_Status.Status like
> '%activating%' )
> and ATX_Server.Server_Status=1 and ATX_Status.isAlive=1
> and ATX_Status.Server = 'ALIL-CHN RLAB'
> and ATX_Status.System = 'ETSI-NFXSA-NANTA-ISAM-01'
> and ATX_Status.Load = 'SD_4003.418' and str_to_date(ATX_Status.Timestamp,
> '%m%d%Y')
>
> thanks in advance
>
> Cheers
> Faizal S
> GSM : 9840118673
> Blog: http://oradbapro.blogspot.com
>



--
Claudio

--0016e6407d7ca224d0047d0b88aa--

Re: mysql query takes too much time to run

At 07:00 AM 1/13/2010, F.A.I.Z.A.L wrote:
>where (
>ATX_Status.Status like '%running%' or ATX_Status.Status like '%queued%' or
>ATX_Status.Status like '%migrating%' or ATX_Status.Status like '%loading%'
>or
>ATX_Status.Status like '%configuring%' or ATX_Status.Status like
>'%activating%' )

It looks like it is doing a full table scan on ATX_Status because you have
started the comparison value with '%....'. It would be faster if you could
do an exact match. Try replacing this "( )" part of the Where clause
with just " (ATX_Status.Status = 'xxxx')"

where xxxx is a valid status like 'running'. Also do an Explain on your SQL
statement to see which indexes it is using. You need to simplify the SQL
statement until it starts running fast again to see what is causing the
slow down. Perhaps rebuild the query with one table join at a time.

Remember to run a "Reset Query Cache" between benchmarks to eliminate the
query cache.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org