For expert: How can I update JAVA local items (data/rows) of user which stored on the MySQL server - вопрос №3523420

I have application and I need often to update items in application. My database is MySQL. Which are technology for it? In this moment I have Java thread which sending request by HTTP to server and getting response in JSON Array:

<code><span class="pun">[</span><span class="pun">{</span><span class="str">"id"</span><span class="pun">:</span><span class="lit">1</span><span class="pun">,</span><span class="str">"refresh"</span><span class="pun">:</span><span class="str">"BDADA61B-494D-412C-BF2D-5D15FCB2F633"</span><span class="pun">},</span><span class="pun">{</span><span class="str">"id"</span><span class="pun">:</span><span class="lit">2</span><span class="pun">,</span><span class="str">"refresh"</span><span class="pun">:</span><span class="str">"58D7D0A5-2BD8-4CD0-9185-D569145175C6"</span><span class="pun">}</span><span class="pun">]</span></code>

While on the server EVERYTIME executing BIG QUERY like this when saving 8 billions rows in

<code>trips_participants</code>

and query query executing every ONE second by

<code>100.000</code>

users:

<code><span class="pln">SELECT </span><span class="str">`id`</span><span class="pun">,</span><span class="str">`refresh`</span><span class="pln"> FROM </span><span class="str">`trips`</span><span class="pln"> WHERE </span><span class="str">`id`</span><span class="pln"> NOT IN </span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="str">`trips_hidden`</span><span class="pln"> WHERE </span><span class="str">`user`</span><span class="pun">=</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> AND </span><span class="str">`id`</span><span class="pln"> IN </span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="pun">(</span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`id`</span><span class="pln"> AS </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="str">`trips`</span><span class="pln"> WHERE </span><span class="str">`driver`</span><span class="pun">=</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> UNION </span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="str">`trips_auction`</span><span class="pln"> WHERE </span><span class="str">`driver`</span><span class="pun">=</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> UNION </span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`id`</span><span class="pln"> AS </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="str">`trips`</span><span class="pln"> WHERE </span><span class="str">`id`</span><span class="pln"> IN </span><span class="pun">(</span><span class="pln"> SELECT DISTINCT </span><span class="str">`trip`</span><span class="pln"> FROM </span><span class="str">`trips_participants`</span><span class="pln"> WHERE </span><span class="str">`customer`</span><span class="pln"> IS NOT NULL AND </span><span class="str">`status`</span><span class="pun">=</span><span class="str">'pending'</span><span class="pln"> AND </span><span class="str">`latitude_from`</span><span class="pun"><=</span><span class="lit">90</span><span class="pln"> AND </span><span class="str">`latitude_from`</span><span class="pun">>=</span><span class="lit">90</span><span class="pln"> AND </span><span class="str">`longitude_from`</span><span class="pun"><=</span><span class="lit">90</span><span class="pln"> AND </span><span class="str">`longitude_from`</span><span class="pun">>=</span><span class="lit">90</span><span class="pun">)</span><span class="pln"> AND </span><span class="str">`driver`</span><span class="pln"> IS NULL AND </span><span class="str">`status`</span><span class="pun">=</span><span class="str">'pending'</span><span class="pun">)</span><span class="pun">)</span><span class="pln"> AS </span><span class="str">`*`</span><span class="pun">);</span></code>

After that I checking for change from response.If there are changes I send other query for updating MAIN item structure.

If I use this method my the database will dead. How can I optimize this?

Thank you for any reply and advice!!!

ноябрь 19, 2019 г.