Would this reduce table locking?

[Prodigal Son]

I have a table with about a dozen columns of data. One of the columns serves as a hit counter. The value is incremented each time with an update statement. So there will be frequent selects and updates which can cause some table locking. If I separate the hits into another table, would that reduce the table locking or would that not really make much of a difference? Now each time the page is visited only the table with just the hits will be updated, but I'd still need to join to that table to display the hits, which I'm wondering will it end up being the same?

[PFMaBiSmAd]

frequent selects and updates

To add one to a column, you don't need to select that value, increment it, then update the value. You can just update the value -

 

UPDATE your_table SET your_column = your_column + 1 WHERE ...

[Prodigal Son]

frequent selects and updates

To add one to a column, you don't need to select that value, increment it, then update the value. You can just update the value -

 

UPDATE your_table SET your_column = your_column + 1 WHERE ...

Oh, I'm not doing that. I am using an update statement exactly like the one you are using. But what I meant is that there are frequent selects on that table to retrieve information.

[Prodigal Son]

Anyone know if that would improve the situation or would it still be the same?

[corbin]

What engine is being used for the table?  I recommend changing the engine to InnoDB if it's MyISAM.

 

 

With MyISAM for a row to be changed, the entire table must be locked, but with InnoDB, locking can be done on a row level.

[Prodigal Son]

What engine is being used for the table?  I recommend changing the engine to InnoDB if it's MyISAM.

 

 

With MyISAM for a row to be changed, the entire table must be locked, but with InnoDB, locking can be done on a row level.

I tried InnoDB but it ended up being even worse since the queries generate a lot of count(*)'s, temp tables, etc.

[corbin]

Well, to fully understand the situation, we'll really need to know more.

 

Can you show us the table schema (including indexes)?  And a list of common queries could be helpful too.

 

 

A simple:

 

 

UPDATE blah SET hits = hits+1 WHERE user_id = {$user_id};

 

With InnoDB as the engine shouldn't involve any temp tables >.<.  I'm assuming you mean other queries do?

[Prodigal Son]

Hmm, I don't think I need it to be too complicated. Right now the table would look like:

 

Info

id |Col 1 |Col 2 |col 3 |col 4 |hits

---------------------------------------------

1 |blabla |blabla |bla |etc |100

 

 

So if I run "UPDATE info SET hits = hits + 1 WHERE id = 1" each time a page is loaded and in addition use select statements to grab info it'll lock the table.

 

I'm basically wondering if I separate the hits into another table would it improve the situation or would it not really matter?

 

So then I would have:

 

Info

id |Col 1 |Col 2 |col 3 |col 4

--------------------------------------

1 |blabla |blabla |bla |etc

 

Hits

id |hits

-------------

1 |100

 

So now I would only run the update on the hits table instead of the info table. But if I want to grab the hits I'll need to join the tables, is that still the same as selecting and updating the same table or would this be better? Hopefully you get what I mean now.

[corbin]

The only time a table is locked is if the data is being changed.  For example, INSERT, UPDATE, DELETE, and ALTER will all cause a lock.

 

 

In InnoDB the lock will only be on the rows being affected by the update.  Under MyISAM the entire table will be locked.

 

 

Also, you do have an index on id, yes?  If not, then whether you use MyISAM or InnoDB it will cause a full table lock.

 

 

 

 

Another thing that can affect if switching to InnoDB would be any use for you....

 

Is it the same id being used over and over again or are different ids constantly having their hits updated?  If it's different ids, then InnoDB would almost definitely be better.

[Prodigal Son]

Yea my first thought was InnoDB would improve my problem, but it actually made it several times worse.

 

And yep, it is multiple id's that have their hits updated. I'm still just wondering if separating the hits to another table will make things better or not. I'm thinking it won't help.

 

Is it the hits table or the info table that would be locked? If its the info table then I couldn't grab data from it, and if the hits table, then I wouldn't be able to join to it either right if I want to display the hits? Hehe I think I just made things complicated, I guess I kind of answered my own question now.

[corbin]

If it's different IDs, then InnoDB should help.

 

 

What indexes do you have on your table?

 

 

The table with the hits column will lock for both reading and writing. 

 

 

Unless you're table is horribly indexed, I'm not quite sure how InnoDB could've decreased performance....

[markwillis82]

COUNT(*) on innodb without WHERE clauses will be slower with INNOdb, and if you are joining tables between multiple engine types (myisam to innodb) you may see a small performance decrease.

 

I would suggest moving the hit count out of the main table for 3 reasons:

 

1) Table level locking - keeping the table as MyISAM will give excellent SELECT speed - but at the cost of table level locking - so connections will be waiting for updates to be processed.

 

2) Query Cache - If your application is running the same queries on the table constantly - they will be stored in the query cache, but if the underlying table is constantly being updated, this will invalidate the query in the cache - rendering it useless and the next time the query runs it will need to hit the table again.

 

3) Faster updates - As you will have a very small hit count table, the updates will run much quicker  - also as the table will be fixed row length, it will be quicker to find results.

 

Hope this helps

 

Mark Willis

[Prodigal Son]

Yea, I'm not quite sure why InnoDB didn't help me out either. I'll try moving the hits column out of the table and see if there is any improvement in performance. Just wanted to make sure it would actually make a difference before doing it and it looks like there are. Thinking about it, there shouldn't be any harm in doing so since even if it doesn't help, it shouldn't hurt.