Strange Mysql Query Performance on View

I have a view : vcompanyendofday

The following query executes in just 0.7 secs

Select * from vcompanyendofday

But a simple where condition to this query takes around 200.0 secs

select * from vcompanyendofday where companyid <= 51;

This is the view definition:

 CREATE VIEW `vcompanyendofday` AS 
  select  `c`.`companyid` AS `companyid`,
          `c`.`scripcode` AS `scripcode`,
          `e`.`eoddate` AS `eoddate`,
          `e`.`prevclose` AS `prevclose`,
          `e`.`delqty` AS `delqty` 
  from    (
          `company` `c`
  left join
          `endofday` `e`
  on      ((`c`.`companyid` = `e`.`companyid`)))
  where   (`e`.`eoddate` =
          (
          select  max(`e2`.`eoddate`) AS `max(eoddate)`
          from    `endofday` `e2` 
          where (`e2`.`companyid` = `c`.`companyid`)
          )
  );

From stackoverflow

• Have you tried the select used to create the view by itself with the WHERE clause to see what happens?

  If the problem happens with that, run EXPLAIN on that query to see what's happening.

  At a guess, there's no index on companyid in one of the tables, most likely endofday.

  Prabu : why doesn't it execute the where condition on the result of the first query? so that it takes only 0.7 secs + few secs.

  R. Bemrose : When you run a WHERE on it, it takes the full result set then filters it. If the column it's filtering on isn't indexed, it does a full table scan.

• Seems you don't have an index on endofday.companyid

  When you add the condition, company becomes leading in the join, and kills all performance.

  Create an index on endofday.companyid:

  CREATE INDEX ix_endofday_companyid ON endofday(companyid)
  

  By the way, if you want all companies to be returned, you need to put the subquery into the ON clause of the OUTER JOIN, or your missing endofday's will be filtered out:

  CREATE VIEW `vcompanyendofday` AS 
  select  `c`.`companyid` AS `companyid`,
          `c`.`scripcode` AS `scripcode`,
          `e`.`eoddate` AS `eoddate`,
          `e`.`prevclose` AS `prevclose`,
          `e`.`delqty` AS `delqty` 
  from    (
          `company` `c`
  left join
          `endofday` `e`
  on      `c`.`companyid` = `e`.`companyid`
          AND `e`.`eoddate` =
          (
          select  max(`e2`.`eoddate`) AS `max(eoddate)`
          from    `endofday` `e2` 
          where (`e2`.`companyid` = `c`.`companyid`)
          )
  

  Prabu : cool! it works. Thank u 1st query takes 2.5 seconds and 2nd query takes 0.02 secs.