Welcome to WebmasterWorld Guest from 54.205.126.164

Forum Moderators: open

Message Too Old, No Replies

Select Count() Performance Options

any other method for achieving this across multiple tables?

     
6:04 pm on Sep 4, 2007 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts:202
votes: 0


I have a small application where a user logs in with a username/password, upon successfully logging in a set of navigation links are displayed on the left of the screen.

These links interface with the different options available in the application, however each link displayed may have a COUNT() value associated with it. For instance one link may be MESSAGES, this particular user may have 3 new messages in the messages.table, the next link may be TASKS, with 4 new tasks in the tasks.table.

I know I could perform a SQL count() on each of the desired tables but I am worried somewhat about the overhead of this (say I have to perform 20 different sql counts) on each page load to display these count numbers.

Has anyone run into this situation? Can your perform counts across multiple tables? One option that crossed my mind was storing these numbers in the users.table but this seems like allot of extra sql-footwork to keep the numbers synced...

1:46 am on Sept 9, 2007 (gmt 0)

New User

5+ Year Member

joined:June 19, 2007
posts:27
votes: 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.

8:20 pm on Sept 11, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2162
votes: 0


Why not set the counters at the start of a session, store them in a cookie and only update them when needed.
8:28 pm on Sept 11, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts:393
votes: 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.
3:45 pm on Sept 12, 2007 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 8, 2004
posts:1679
votes: 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.
1:27 am on Sept 21, 2007 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 30, 2003
posts: 428
votes: 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

1:31 am on Sept 29, 2007 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts: 202
votes: 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!

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members