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?
Subscribe to get latest updates