I think you're reducing the amount of rows referenced throughout the proc using the view. This might be where you're seeing a performance difference. If you create an innodb table where the structure and row count match the view maybe you'll see another difference? I'll wait for Rick James' input before I say anything more. ;-)
On Tue, Jul 30, 2013 at 12:13 PM, Manivannan S. < manivanna...@spanservices.com> wrote: > Hi, > > 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. > > Regards > 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 mailad...@spanservices.com 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. >
