Re: Store and updating large number of columns

On 03/19/18 17:02, forough m wrote:
> Hi,
> I have many cells (10000 per hour) that each cell has 1000 counters(Keys and Values that comes during hours and need to update). I need to store cell name and counters, then calculate some formula on counters. But i don't know how to keep counters in MySql to having best update and select query performance.
> Now i keep them in Josnb in Postgresql, and want to store them in MySql.
> MySql Json has not index feature and it make slowly query. what do you suggest me?

I think you need to use indirect indexing as explained at: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

Alternative could be using the graph-database for "indexing" by storing the key-value together with the primary key. Each time you update data, you update the right value in the graph-database and then regenerate the json from the graph-database and then store it in the json column. Sure the search in the graph-database will be fast, but not sure how fast the regenerating of the json and the initial conversion from json to graph-database will be, it can in the long run be just slower than the non-indexed json object you already have.

But I would recommend you stay with Postgresql.

-- 

 //Aho