Any MySQL experts here?
By sundarforu
@sundarforu (36)
India
June 22, 2008 9:45pm CST
I have large mysql table with 2 million records. I have to run a query to find moving average on each record. I have written the script. But it takes very long time to complete. I need to optimize the query to speed up. Any suggestions would be appreciated. Thanks
2 responses
@agihcam (1914)
• Philippines
25 Jun 08
Ok.Don't know the exact codes you have created and using,but seems it is running fine and you can output the results you wanted,however,the problem you are having is the speed of your code performance.Without seeing your code,I suspect that it was the computations code ot maybe some looping process that cause slowness of your query.
1 person likes this
@sundarforu (36)
• India
25 Jun 08
I have found the solution. I indexed the two fields date and company, now the speed is 100 times than before. Anyway thanks for the response.
@sundarforu (36)
• India
23 Jun 08
Thanks for the post.
I have a table with records of stock values of companies. There are about 3000 companies, and for each company about 1000 records. That means daily closing stock values of these companies. Data from 01-01-2005 to till date. So about 3 million records on a single table. I want to calculate Moving average for 50 days and for 200 days. Average for a particular day means average for the past 50 days inclusive of that day. In this manner i have to calculate average for each day for each company. There is another average for 200 days to be found. These values to be update in the same table whose values are currently NULL. Once it is calculated i have decided to create a trigger to automatically update it.
My problem is the MySQL query is very slow. It takes about 10 hours per company meaning 30000 hours to process all records. Please give a suggestion to optimize. Thanks.
