Bdtjtk

decreasing running time (select query executing time)

even query is not working

it is reducing code line

there are 80 tables

select 

     (ant_return_loss_pass='pass')+(ant_cross_isolation_pass='pass') as pass_count,

     (ant_return_loss_pass='fail')+(ant_cross_isolation_pass='fail') as fail_count,

     (ant_return_loss_pass='')    +(ant_cross_isolation_pass='') as blank_count

from 

(

  select A.serial_no,

         A.pass_fail as ant_return_loss_pass    

  from ant_return_loss A,

  (                                

      select max(register_date) as date

        from ant_return_loss 

        where 1=1

          and serial_no >= '184500074' 

          and serial_no <= '184500076' 

        group by serial_no

  ) B                                 

  where 1 = 1                     

    and A.register_date = B.date     

)AA

,(

  select A.serial_no,

         A.pass_fail as ant_cross_isolation_pass    

  from ant_cross_isolation A,

  (                                

      select  max(register_date) as date

        from ant_cross_isolation 

        where 1=1

          and serial_no >= '184500074' 

          and serial_no <= '184500076' 

        group by serial_no

  ) B                                 

  where 1 = 1                     

    and A.register_date = B.date     

)BB

where 1=1

  and AA.serial_no = BB.serial_no

not working or working 600 sec

The query below uses explicit join syntax and case expressions to make you query easier to understand:

SELECT

    COUNT( CASE WHEN ant_return_loss_pass = 'pass' AND

                     ant_cross_isolation_pass = 'pass' THEN 1 END ) AS pass_count

  , COUNT( CASE WHEN ant_return_loss_pass = 'fail' AND

                     ant_cross_isolation_pass = 'fail' THEN 1 END ) AS fail_count

  , COUNT( CASE WHEN ant_return_loss_pass = '' AND

                     ant_cross_isolation_pass = '' THEN 1 END )     AS blank_count

FROM (

    SELECT

        A.serial_no

      , A.pass_fail AS ant_return_loss_pass

    FROM ant_return_loss A

    INNER JOIN (

            SELECT MAX( register_date ) AS date

            FROM ant_return_loss

            WHERE serial_no >= '184500074'

            AND serial_no <= '184500076'

            GROUP BY serial_no

        ) B ON A.register_date = B.date

    ) AA

INNER JOIN (

        SELECT

            A.serial_no

          , A.pass_fail AS ant_cross_isolation_pass

        FROM ant_cross_isolation A

        INNER JOIN (

                SELECT  MAX( register_date ) AS date

                FROM ant_cross_isolation

                WHERE serial_no >= '184500074'

                AND serial_no <= '184500076'

                GROUP BY serial_no

            ) B ON A.register_date = B.date

    ) BB ON AA.serial_no = BB.serial_no

To examine if this can be improved for performance however requires access to your database. For example do you indexes for ant_return_loss.serial_no or ant_cross_isolation.serial_no these will aid the where clauses of the subqueries.

Have you run any explain plans on the query? see: https://dev.mysql.com/doc/refman/5.7/en/explain.html

Note to avoid long running time you can use explain on separated portions of the overall query, look for indexes that may assist: e.g.

            explain

            SELECT MAX( register_date ) AS date

            FROM ant_return_loss

            WHERE serial_no >= '184500074'

            AND serial_no <= '184500076'

            GROUP BY serial_no

then:

    explain

    SELECT

        A.serial_no

      , A.pass_fail AS ant_return_loss_pass

    FROM ant_return_loss A

    INNER JOIN (

            SELECT MAX( register_date ) AS date

            FROM ant_return_loss

            WHERE serial_no >= '184500074'

            AND serial_no <= '184500076'

            GROUP BY serial_no

        ) B ON A.register_date = B.date

until you have examined all portions for way to improve performance.