List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 30 2013 3:25pm
Subject:RE: Performance Improvements with VIEW
View as plain text  
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

Thread
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