MySQL reducing number of query onclick - asynchronous updates

Question

I've got a map, with hundreds of action listener (one for each position of the map), when the user click on one cell/tile of the map he gets more information about this cell (information are displayed in the same page).
I want to store in a MySQL database the number of times that a cell has been clicked.

The problem is that I want to let a user to click and gain information quickly (-->the user will click often), and doing a query every time he clicks slows down performances. Moreover having a huge number of user doing a query for every click, and a significant number of click for every user, will fill my database bandwidth.

So I want to have a "buffer" on client that stores the clicks, and update/insert them into my database Asynchronously (for example doing the query when the user leave page, after a number of clicks, after tot seconds, ... ). I know I can do it with JS but it seems ugly to me, isn't it? How would you do it? (code is appreciated but not mandatory)

Summary: I want to avoid doing a query on click, I want to do a query after a set of clicks (reducing the number of query) and without losing clicks.

You could use a background job to accumulate the clicks and then store them in a database. — axxis
– axxis, Commented May 7, 2015 at 11:16

Rick James · Accepted Answer · 2015-05-08 04:22:00Z

Each click must go to some central server to be handled. JavaScript --> AJAX --> PHP (or other cgi) is the obvious (and only?) answer.

So, you need to look at another performance question. How many clicks per second can the cgi handle? The limit there is the same order of magnitude as the number of counter UPDATEs that MySQL can handle.

If you need to handle thousands of clicks per second, you will need to have multiple web servers. This will complicate things even more. I will assume for this discussion that all the processing can be done in a single server.

So, let's look at another possible solution. Can PHP access some form of 'shared' memory? If so, all the connections can bump counters in that memory instead of some MySQL table. There would need to be some form of locking to keep from losing counts. Look into that.

OK, let's say you decide you really need to keep the updates in a database. Use InnoDB. Use transactions if it is more complex than a single SQL statement, and handle deadlocks.

I think MySQL is not the limiting factor. Do you have 'proof' to the contrary? If so, let's see the SQL that is involved, plus SHOW CREATE TABLE.

Also look into Redis.

Collectives™ on Stack Overflow

MySQL reducing number of query onclick - asynchronous updates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related