Strange Mysql query performance while browsing
I have a view: vcompanyendofday
The next request is executed in just 0.7 seconds
Select * from vcompanyendofday
But just when the condition for this request takes about 200.0 seconds
select * from vcompanyendofday where companyid <= 51;
This is the definition of the view:
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`)
)
);
source to share
You don't seem to have an index on endofday.companyid
When you add a condition, company
it becomes the lead 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 a subquery in the sentence ON
OUTER JOIN
, or your missing one endofday
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`)
)
source to share
Have you tried the choice used to create the view on its own with a WHERE clause to see what is going on?
If you have a problem with this, run EXPLAIN
in this query to see what is happening.
Guess there is no index on companyid on one of the tables, most likely at the end of the day.
source to share