Strange Mysql Query Performance on View

Question

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`)
          )
  );

Answer 1

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.

Answer 2

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`)
        )