alexdunae
msg:3445383 | 1:46 am on Sep 9, 2007 (gmt 0) |
I've always had good luck with using a counter field (e.g. `users`.`message_count`) and incrementing/decrementing as required. It doesn't take that much more work it will be fast. That said, COUNT() is generally very fast providing you have indexes set up properly.
|
aspdaddy
msg:3447834 | 8:20 pm on Sep 11, 2007 (gmt 0) |
Why not set the counters at the start of a session, store them in a cookie and only update them when needed.
|
syber
msg:3447848 | 8:28 pm on Sep 11, 2007 (gmt 0) |
COUNT(*) is usually very fast in that the table itself usually doesn't need to be accessed (the meta information in the database should already have stored the number of records in each table). If you say COUNT(fieldname), however, the actual table (or index) must be accessed to determine the number of rows without NULL in the fieldname column.
|
Lord Majestic
msg:3448592 | 3:45 pm on Sep 12, 2007 (gmt 0) |
Using MAX() on a column that is incremented for each row can often do the trick very quickly so long as it is indexed. If you read stats more than you update, then you could benefit from updating stats in place whenever new data is added - triggers could be good for that, but be careful as excessive use of triggers can lead to performance issues. Alternatively you can just update stats once every X minutes - they won't be exact, but if you have lots of data then running stats in real time would cost too much.
|
SeanW
msg:3456661 | 1:27 am on Sep 21, 2007 (gmt 0) |
Create an index on the field that links the user to the row, mysql should be able to count it quickly (your have tuned your server so the indexes stay in memory, right?) The mysql query cache might help depending on the write volume to the table. Failing that, look into memcached. Store the whole profile as a serialized array (messages=3, tasks=2), and invalidate the key if one of them changes. Sean
|
blaketar
msg:3464332 | 1:31 am on Sep 29, 2007 (gmt 0) |
Thanks guys, I hadnt thought of the session variable idea but created a function which updates the number array when needed and it works great! Thanks Again!
|
|
