samedi 11 juin 2016

Saving regular SQL result array as an entry in another table do reduce queries number, good idea or not?

I had an idea and I wonder if its completely stupid or a good idea.

I'm working on a web app which analyze GPX tracks and give stats. Actually I'm storing the coordinates of each points of each tracks in a table, this way :

point_id | track_id | lat | lon | else | speed | timestamp

and each time I'm selecting entries from this table, I do it by track_id :

 SELECT * FROM points WHERE 'track_id'=12

And so I'm getting a full array that I then process to get it formatted for example for my map script :

[[46.323, 7.543, 1465663049], [46.323, 7.543, 1465663049], [46.323, 7.543, 1465663049]]...

Or for my speed graph script :

[[1465663049, 9, 12], [1465663049, 9, 12], [1465663049, 9, 12], [1465663049, 9, 12]...

But I think that making a query involving hundreds of entries to process them the same way every time is a bit unproductive so I was wondering if I could make a kind of buffer table with my processed results, for example :

track_id | map_array | graph_array 

So I would only have to make one query to get my array, but it means that the field will be quite big as they would need to store thousands of characters an entry.

Maybe a best choice would be to store them in a json datatype field so it can be easily used by my scripts ?

Do you think it is a good idea ?

TL;DR : Storing gps tracks point in several entries or storing them in a single entry with json datatype ?

Thank you for reading me.

François

Aucun commentaire:

Enregistrer un commentaire