VIEWs are not well optimized. Avoid them.
The SlowLog will probably point to the worst query; we can help you improve it (SHOW
CREATE TABLE; SHOW TABLE STATUS; EXPLAIN)
Only minutes to go through 10 million records? Sounds good. It takes time to shovel
through that much stuff.
"Sending data" (etc) -- yeah these states are "useless information" in my book. They
merely say "you have a slow query". "Sorting results" probably implies a GROUP BY or
ORDER BY. It _may_ be possible to avoid the sort (when we review the naughty query).
What kind of things are you doing? If Data Warehouse 'reports', consider Summary Tables.
Non-trivial, but the 'minutes' will become 'seconds'.
> -----Original Message-----
> From: Bruce Ferrell [mailto:bferrell@stripped]
> Sent: Tuesday, July 30, 2013 7:08 AM
> To: mysql@stripped
> Subject: Re: Performance Improvements with VIEW
> On 07/30/2013 04:13 AM, Manivannan S. 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
> If you turn on your slow queries logs and activate log queries without
> indexes, I suspect you'll find your answer.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql