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:bferr...@baywinds.org] > Sent: Tuesday, July 30, 2013 7:08 AM > To: mysql@lists.mysql.com > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
