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. <
> 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
> 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.