Find Jobs
Hire Freelancers

Speedup a MySQL Query which collectes Data from a Database for displaying on php Graph

€8-30 EUR

完了済み
投稿日: 約6年前

€8-30 EUR

完了時にお支払い
I've got a php page which shows data from a mysql database. The database contains datetime, weight, temperature, weight-correction values and so on. For displaying the data, my query collects all data. Therefore are first all weight values with the correction value be calculated. This takes longer as the database increasese because the calculation eatch time must start from the first value to the last, to do not miss any correction values. Simlyfied example: datetime | weight | correction | corrected 20180221 11:00 | 100 | 0 | 100 20180221 12:00 | 101 | 0 | 101 20180221 11:00 | 100 | 5 | 105 20180221 12:00 | 101 | 0 | 106 20180221 13:00 | 99 | -1 | 103 The actual query looks like this: SELECT DISTINCT UNIX_TIMESTAMP(DATE([login to view URL])) AS datetime_unix, [login to view URL], ( [login to view URL] + (100*(SELECT SUM(correctionvalue) FROM zzz_data_2 b WHERE [login to view URL] <= [login to view URL] AND [login to view URL] = '21' ORDER BY datetime ))) as weight FROM zzz_data_2 t1 LEFT JOIN zzz_data_2 t2 ON (DATE([login to view URL]) = DATE([login to view URL]) AND [login to view URL] > [login to view URL] AND [login to view URL] = '21') WHERE [login to view URL] >= ('2018-01-01' - INTERVAL 0 DAY) AND [login to view URL] <= ('2018-02-20' + INTERVAL 1 DAY) AND [login to view URL] IS NULL AND [login to view URL] = '21' ORDER BY [login to view URL] The query takes 30sec to be executed. This is much to long. I would have it to be as quick as 1sec or less.
プロジェクト ID: 16342881

プロジェクトについて

9個の提案
リモートプロジェクト
アクティブ 6年前

お金を稼ぎたいですか?

Freelancerで入札する利点

予算と期間を設定してください
仕事で報酬を得る
提案をご説明ください
登録して仕事に入札するのは無料です
アワード者:
ユーザーアバター
Hi, I have gone through your description of the issue as well as the sample sql statement and have understood the issue you are facing. Essentially, in order to calculate the corrected value of the weight, you need to look at all the correction values for the preceding period (within a given datetime range). This needs to also be based on the ID value in the table (assuming that you ave multiple id's in the table each having a different set of correction values over a period of time). I believe i can help you get the time taken for the sql query to run to a subsecond response time. I plan to use stored procedures to achieve this. I have over 17 years of designing and developing software applications and have worked with multiple database technologies (mysql, oracledb, nosql, mongodb, sqllite etc) as well as multiple programing platforms (java, javascript, php, python, c, shell scripts etc). As you would see from the reviews I have received on the freelancer platform, I am a serious freelancer who has a 100% completion rate with a 5 star rating. Over 33% of the projects i have done have resulted in a rehire because the employees liked the work that i did. Looking forward to a positive response from you. thanks
€29 EUR 1日以内
5.0 (1 レビュー)
1.4
1.4
この仕事に9人のフリーランサーが、平均€27 EURで入札しています
ユーザーアバター
Hi! First of all, that SQL statement you are giving as what it woul dlook like is generallyerroneous. Because "AND"-ing the datetime field with an "IS NULL" condition shouldn't be returning any results at all, assuming this column can never be empty. So it would be better if you can forward the actual code to assess what the actual conditions on the query are/would be. That's one thing. Now, whether there's something logically wrong in teh way you are doing the weight calcultion, that is impossible to assess given the very little factual information you have provided, so this is something to be toucheed on further. I'd like to see the table definition statment as well. You did not provide any info as to column indexes etc. Also, I am assuming the old records never change, only new ones are added. Is this assumption correct or not? Kind Regards, Dobri
€24 EUR 0日以内
5.0 (168 レビュー)
6.1
6.1
ユーザーアバター
Hi there! This is Raj from India. I have 10+ years of concrete experience in web development. I specialize in PHP Frameworks like Yii, Yii2, Laravel and CMS like WordPress, Joomla, Magento and other associated platforms. We could probably connect for a detailed discussion over the requirements as per your convenience. I can give a feasible solution to speed up the response time as mentioned in your requirements. I am ready to start work immediately! Looking forward to connecting with you! Thanks & Regards, Raj S
€23 EUR 1日以内
5.0 (2 レビュー)
3.5
3.5
ユーザーアバター
A proposal has not yet been provided
€50 EUR 1日以内
5.0 (9 レビュー)
3.4
3.4
ユーザーアバター
Hi, good day. You should try to add Some indexes in your tables, this can help with speed of select but it could take one second more to insert data. I could help you if you wish. Feel free to contact me
€10 EUR 5日以内
4.9 (26 レビュー)
3.6
3.6
ユーザーアバター
Hi - As i see the query you are using functions in the select clause so indexes are not going to be used. If you can show the table strructure i would be able to assess where you can tune. Let me know.
€23 EUR 1日以内
5.0 (1 レビュー)
0.0
0.0

クライアントについて

GERMANYのフラグ
Neunburg vorm Wald, Germany
5.0
3
お支払い方法確認済み
メンバー登録日:3月 5, 2016

クライアント確認

ありがとうございます!無料クレジットを受け取るリンクをメールしました。
メールを送信中に問題が発生しました。もう一度お試しください。
登録ユーザー 投稿された仕事の合計
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
プレビューを読み込み中
位置情報へのアクセスが許可されました。
あなたのログインセッションの有効期限がきれ、ログアウトされました。もう一度ログインしてください。