Search code examples
mysqlpercona

Quickest way to increment counter in MySQL DB

I'm running a forum on a VPS, running Percona DB, with PHP 5.5.8, Opcode caching, etc, it's all very speed orientated.

I'm also running New Relic, (yes I have the t-shirt).

As I'm tuning the application, optimising queries the forum is making to the DB for any query at the top of my time consumed list.

Right now, the most time consuming query I have, as it's the most frequently used is a simple hit counter on each topic.

So the query is:

UPDATE topics SET num_views = num_views + 1  WHERE id_topic = ?

I can't think of a simpler way to perform this, or if any of the various other ways might be quicker, and why.

Is there a way of writing this query to be even faster, or an index I can add to a field to aide speed?

Thanks.

Solution

  • Assuming id_topic is indexed, you're not going to get better. The only recommendation I would have is to look at the other indexes on this table and make sure you don't have redundant ones that include num_views in them. That would decrease update speed on this update.

    For example if you had the following indexes

    1. ( some_column, num_views)
    2. ( some_column, num_views, another_column)

    Index #1 would be extraneous and just add to the insert/update overhead