Suoritettu

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

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([url removed, login to view])) AS datetime_unix, [url removed, login to view],

( [url removed, login to view] + (100*(SELECT SUM(correctionvalue) FROM zzz_data_2 b WHERE [url removed, login to view] <= [url removed, login to view] AND [url removed, login to view] = '21' ORDER BY datetime ))) as weight

FROM zzz_data_2 t1 LEFT JOIN zzz_data_2 t2 ON (DATE([url removed, login to view]) = DATE([url removed, login to view])

AND [url removed, login to view] > [url removed, login to view] AND [url removed, login to view] = '21')

WHERE

[url removed, login to view] >= ('2018-01-01' - INTERVAL 0 DAY)

AND

[url removed, login to view] <= ('2018-02-20' + INTERVAL 1 DAY)

AND

[url removed, login to view] IS NULL

AND

[url removed, login to view] = '21'

ORDER BY [url removed, login to view]

The query takes 30sec to be executed. This is much to long. I would have it to be as quick as 1sec or less.

Taidot: HTML, MySQL, PHP

Näytä lisää: how to display data from database in php form, how to display data from database in php in table, display mysql data in php table, php code to retrieve data from mysql database and display, display data in table format in php, display mysql database on web page, display data on web page from database, how to fetch data from database in php and display in html table, mysql fundamental stock data database design, php mysql insert data database, update data database prototype php, save data database as3 php, oracle query show data database, mysql query fetch data remote database, convert data database numbering php, php mysql copy data database, php mysql data database excel sheet, show data database marquee php, php mysql procurring data database, export data mysql database xls php

Tietoa työnantajasta:
( 3 arvostelua ) Neunburg vorm Wald, Germany

Projektin tunnus: #16342881

Myönnetty käyttäjälle:

panditthecoder

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 t Lisää

€29 EUR 1 päivässä
(1 arvostelu)
1.5

11 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön

€33 EUR 1 päivässä
(966 arvostelua)
8.7
justTry

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, assum Lisää

€24 EUR 0 päivässä
(155 arvostelua)
6.0
Aromday

Good day! My name is Mike and I provide Web design and development . My principles are simple, clean and beautiful in the details. I am interested in your project because I have done similar projects in the past. Lisää

€23 EUR 1 päivässä
(10 arvostelua)
4.9
€23 EUR 1 päivässä
(17 arvostelua)
4.6
arkraj

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 platf Lisää

€23 EUR 1 päivässä
(2 arvostelua)
3.5
jomingeorge94

A proposal has not yet been provided

€50 EUR 1 päivässä
(9 arvostelua)
3.5
user2690

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 päivässä
(17 arvostelua)
3.3
DnyaneshwerK24

Dear Hiring Manager, Hope you are doing well I have gone through your project description i can develp that you mentioned in description We have huge experience of website design, mobile application for both Android Lisää

€23 EUR 1 päivässä
(6 arvostelua)
2.4
kdwebsolution9

Hi, We can help you to Speedup a MySQL Query which collects Data from a Database for displaying on PHP Graph as per needs. Could you please share Cpanel, Admin credential and FTP details? So we can review it. As Lisää

€29 EUR 0 päivässä
(3 arvostelua)
1.2
supergana

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 päivässä
(1 arvostelu)
0.1