MySQL: views=views+1, how fast is it? ( 6 Views )

1. Well, it is faster than views=### ?
   (The number of views comes from the initial query)
   
   UPDATE tbl SET views=views+1 WHERE id=##
   vs
   UPDATE tbl SET views=### WHERE id=###
   
   
   (asdasd, Korea, Democratic People's Republic of)


2. I don't understand why you would want to use that with these queries.
   
   
   (pnar, Spain)


3. How about using it in conjunction with register_shutdown_function? Would it worth the extra overhead? hmmm
   
   Thanks! :)
   
   
   (Sinan, Kuwait)


4. Quote:

   Originally Posted by Daijoubu While MySQL is waiting, does it affect the end user? (The one who's running the UPDATE/INSERT) IE: Would PHP wait for the query or is it done in "background" ? Edit: Thanks for moving it, didn't knew there was a MySQL forum :)

   The LOW_PRIORITY keyword causes the query to 'wait'. PHP would also wait. It can't be done in the background, because PHP needs to know whether the query was successful and how many rows were updated. There is one exception to this, with an INSERT, you can have it done in the background with INSERT DELAYED.
   
   If you want your UPDATE to be fast, don't use LOW_PRIORITY. Use LOW_PRIORITY if you want waiting SELECTs to have higher priority than this UPDATE. This will only really matter if you have a very high level of traffic.
   
   
   (ali rýza, Kazakhstan)


5. While MySQL is waiting, does it affect the end user? (The one who's running the UPDATE/INSERT)
   
   IE: Would PHP wait for the query or is it done in "background" ?
   
   Edit: Thanks for moving it, didn't knew there was a MySQL forum :)
   
   
   (orospu rüya sönmezz, Kuwait)


6. Quote:

   Originally Posted by Daijoubu UPDATE tbl SET views=views+1 WHERE id=## vs UPDATE tbl SET views=### WHERE id=###

   All else being equal, these two commands are almost exactly the same in terms of efficiency, any difference being negligible.
   
   However, they're used for different purposes. The first one should be used when you want to increment the number of views by one. The second one should be used when you want to set the number of views to a specific value regardless of its current value.
   
   UPDATEs will always require a write lock, and because a write lock is more restrictive than a read lock, it will always have a higher priority than any read operation. You can override this with LOW_PRIORITY, which will cause the UPDATE to wait until after any threads waiting for a read lock, keeping in mind that this may cause the update to take a long time.
   
   
   (serdal, Azerbaijan)


7. Any documentations (except mysql.com manual) on using LOW_PRIORITY/HIGH_PRIORITY with UPDATE? :)
   
   
   (serkan, Saint Helena)


8. Why should I blame them? MySQL is by far the most popular free RDBMS and it is widespread on many webhosts around the world. They focused on that first and as they said they will support other RDBMSs in the near future.
   
   
   (GÖNÜL, Congo)


9. Blame vB for supporting MySQL only? (with MyISAM)
   InnoDB feature row level locking if i'm not mistaken, it's slower too.
   
   
   (ÜMMÜ, Greenland)


10. vB 3 stores it in a separate table called threadviews and updates the actual thread table hourly (they coded their own cronjob system to make sure it's being done every hour or at least when the next page is being viewed, whatever is later).
    
    Edit: vB doesn't do that because of the load of the query itself. But if you have a very active board and if the thread-table had to be updated every single time a user views a thread, it's bound to have an effect on performance. So vB just updates a separate table and inserts the content of threadviews into thread every hour.
    
    
    (miraç, Tuvalu)


11. Er, not just accuracy, you're looking at high loads if you do views=views+1 or views=## period. vBulletin caches views and then updates it every hour (can be disabled in admin panel). If you post a thread here, go to it, make a few people look at it, it'll still say 0 views, until the turn of the hour. Handy way of doing it. How? No clue, lol, sorry. :p It might be a heap database that it stores it in and then actually does it properly after.. somebody here'll know.
    
    
    (güzele, Mauritania)


12. I can't save the initial query, it's not only for the number of views ;)
    
    I was aware of accuracy issue, especialy on busy database
    
    Thanks
    
    
    (BAYRAMALÝ, Burundi)


13. You should always use views=views+1.
    Let's say you have a brick and mortar shop and two of your employees just sold a pair of jeans each. Before both transactions you had 43 pairs of jeans and let's just say that, for the sake of argument, both employees run the script simultaneously which updates your inventory.
    If that script first retrieves the value, then decreases it by one and then you update the table again, you end up with 42 pairs of jeans. But wait, you have sold 2 pairs!
    With amount_of_jeans=amount_of_jeans-1 both queries get executed after each other and you end with the (correct) value of 41 pairs. Also you save one query per call to the script which would be needed to fetch the initial amount of jeans. Win-win scenario :) .
    
    
    (melis, Iceland)


14. Well, if there would be any delay, it wouldn't affect the page responce time
    
    
    (muhammet, Lebanon)

Related Topics ... (or search in 1.720.883 topics !)