Techiio-author
Started by Debarghya DasSep 29, 2021

Open
How to optimise reading from the MySQL table that is continuously updating?

0 VIEWES 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 0 VIEWES SHARE

I have an app, that processes price-list from different suppliers and shows some result it with charges. About 1 million items total and 50-100 incoming price-list every day. Data is stored to database with main table Quantity (id, itemId, supplierId, price, quantity).

When price-list is processing, app searches item quantity record and updates it's price and leftovers. (or creates new record). Files with price-lists are processing in queue by 2-4 in a row simultaneously by php app (yii framework)

So issue is that when user tries to search something, mysql server is busy with updating data, and answers takes up to 10-20 seconds. When server idles, search takes 0.5-1 second, which is acceptable.

How can i restructure database and application to make search faster?

0 Replies

You must be Logged in to reply
Techiio-logo

Techiio is on the journey to build an ocean of technical knowledge, scouring the emerging stars in process and proffering them to the corporate world.

Follow us on:

Subscribe to get latest updates

You can unsubscribe anytime from getting updates from us
Developed and maintained by Wikiance
Developed and maintained by Wikiance