Reputation: 417
I have a view which has the below definition
create view mydashboard as
SELECT distinct
cu.CrimeID,
ad.DeptID,
ad.CrimeDate,
cd.DeptIncidentID,
ad.crime,
u.username
from alldatescrimes ad
inner join crimeslist cl
on
ad.crime = cl.crime
inner join users u
on
u.DeptID = ad.DeptID
left join crimelookup cu
on cu.CrimesListID = cl.CrimeListID
left join crimesdetail cd
on
ad.CrimeDate = cast(cd.CrimeDate as date)
and
ad.DeptID = cd.DeptID
and
cu.CrimeID = cd.CrimeID
My problem is that If I put a where clause outside the view, the query runs very slowly. See the below example
select *
from mydashboard
where
(username = 'john'
or
DeptIncidentID is null
)
and
CrimeDate = '2014-06-16'
On the contrary If I put the same where clause inside the view, the query runs very fast..like in 2-3 seconds
My question is what steps can I take so that the query runs fast if I put the where clause outside view. I am using this view in a report and the query runs real slow
Regards Arif
Upvotes: 2
Views: 238
Reputation: 29809
MySQL has two options to process a view that is used inside a query: MERGE
or TEMPTABLE
.
For
MERGE
, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.For
TEMPTABLE
, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
Because of the DISTINCT
clause in the view definition, MySQL cannot use the MERGE
algorithm. It must falback to the less efficient TEMPTABLE
algorithm.
The temporary table has no index, therefore the whole table must be scanned to process your outer WHERE
conditions.
You may want to remove the DISTINCT
clause from the view definition, and put it in your outer query instead.
Upvotes: 1
Reputation: 1271051
This is a bit long for a comment.
MySQL does a poor job of optimizing views. In fact, one part of the documentation starts:
View processing is not optimized:
One possible issue is that MySQL has determined that a temporary table is needed for the view. If so, all the processing needs to be done. Then at the very last stage, the where
clause is being added. Here is more information on "merge" versus "temporary tables" for views.
Upvotes: 0