1
I have a database that stores precipitation marked by various sensors on certain dates and times over the course of a year. I am making a query, to search all records marked by a single sensor, with the following instruction SELECT p.latitude, p.longitude, h.precipitacaoh, h.datah, h.horah FROM pontos AS p, historico AS h WHERE p.gid = 913 and the time spent is around 9.36 minutes. But there are 1517 sensors and I need to recover all the records of each of them, which would total about 9 days searching for all these records. Does anyone know a way to shorten this consultation time?
gidis the primary key of the table? How many more or less records return after this time?– Sorack
I really like this tutorial. http://imasters.com.br/artigo/22264/banco-datas/10-tecnicas-optimizacao-de-instrucoes-sql/? trace=1519021197&source=single
– Marconi
that, gid is the primary key of the points table, after 9.36 minutes returns a total of 4429640 records @Sorack
– Jessica Costa
Thanks @Marconi, I’ll take a look
– Jessica Costa
Isn’t there a link missing between the point table and the history? So you are creating a cross Join.
– Renato Afonso
@Renatoafonso actually, first I’m doing
SELECT DISTINCT gid, latitude, longitude FROM pontosI do one for going through each sensor and inside the for I put the following querySELECT datah, horah, precipitacaoh FROM historico WHERE gidgeo_fk = %d"%(contSensor[0])to take the history of each sensor– Jessica Costa