List:General Discussion« Previous MessageNext Message »
From:Manivannan S. Date:July 30 2013 11:13am
Subject:Performance Improvements with VIEW
View as plain text  

I've a table with 10 Million records in MySQL with INNODB engine. Using this table I am
doing some calculations in STORED PROCEDURE and getting the results.

In Stored Procedure I used the base table and trying to process all the records in the
table. But it's taking more than 15 Minutes to execute the procedure. When executing the
Procedure in the process list I am getting 3 states like 'Sending data', 'Sorting Result'
and 'Sending data' again.

Then I created one view by using  the base table and updated the procedure by replacing
that view in the place of a base table, it took only 4 minutes to execute the procedure
with a view. When executing the Procedure in the process list I am getting 2 states like
'Sorting Result' and 'Sending data'. The first state of 'Sending data' is not happened
with view, It's directly started with 'Sorting Result' state.

When I'm referring some MySQL sites and other blogs, I have seen that VIEWS will never
improve the performance. But here I see some improvements with a view.

I would like to know how VIEW is improving the performance.

Manivannan S

DISCLAIMER: This email message and all attachments are confidential and may contain
information that is Privileged, Confidential or exempt from disclosure under applicable
law. If you are not the intended recipient, you are notified that any dissemination,
distribution or copying of this email is strictly prohibited.  If you have received this
email in error, please notify us immediately by return email to mailadmin@stripped
and destroy the original message.  Opinions, conclusions and other information in this
message that do not relate to the official of SPAN, shall be understood to be nether given
nor endorsed by SPAN.

Performance Improvements with VIEWManivannan S .30 Jul
  • Re: Performance Improvements with VIEWAndrew Moore30 Jul
  • Re: Performance Improvements with VIEWBruce Ferrell30 Jul
    • RE: Performance Improvements with VIEWRick James30 Jul