CODEWITHSUNDEEP
mysqlsqlquery-optimizationquery-performance
Arif
Arif

Reputation: 417

sql view performance optimization

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

Answers (2)

RandomSeed
RandomSeed

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

Gordon Linoff
Gordon Linoff

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

Related Questions