why so slow this mysql query to execute?

select 
    'Owner' as 'Type',
    count(s.shipwynum) as Total,
    o.ownwynum as WYNum,
    o.ownshortnam as 'Short name',
    o.ownnam as 'Full name',
    cio.weburl as 'Web URL',
    count(if(s.statuscod = 'O',1,null)) as 'number of orders',
    count(if(s.statuscod = 'S',1,null)) as 'number of ships' 
from owner o 
left join ship s on o.ownwynum = s.benownwynum and s.deleted = 'N' and
      s.statuscod in ('O','S') and
      s.benownwynum != '' and s.benownwynum !='0' and s.benownwynum is not null 
left join companyinfo cio on cio.objwynum = s.benownwynum and
      cio.deleted = 'N' and cio.comflag = 'OW' where o.deleted = 'N'     
group by o.ownnam

This query runs under a开发者_如何学Got 9 minutes and 55 seconds and retrieve 10,106 records.

My question is why it takes a lot of time to fetch the data and what are the issues to less its time for execution ?

But interesting thing is that I have run this query :=

select * from ship 

It takes 3 minutes and 12 seconds under execution and will fetch 75,672 records.

Any idea to optimize it ?

Unless you are really meaning to avoid null values of s.shipwynum, try this - replace:

count(s.shipwynum) as Total

with

count(*) as Total

It is generally true that COUNT(*) operates faster than COUNT(expression).

Another experiment, replace:

count(if(s.statuscod = 'O',1,null)) as 'number of orders'
count(if(s.statuscod = 'S',1,null)) as 'number of ships' 

with

sum(if(s.statuscod = 'O',1,0)) as 'number of orders'
sum(if(s.statuscod = 'S',1,0)) as 'number of ships'