On 07/30/2013 04:13 AM, Manivannan S. wrote:
> 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
If you turn on your slow queries logs and activate log queries without indexes, I suspect
you'll find your answer.