Optimize multiple counts?

Hi, i am playing around with a simple 'community' project where the users can post msgs in a msgboard. I was wondering how I can speed things up here: Because I've made a list displaying "xx new messages since last login" in each thread of the forum, and to do that i am counting all the msgs in the forum since last the user logged in. Example as follows:

select count(*) from msgs where thread=xx between user.lastlogin and getDate()

This is naturally super-slow, especially when doing 10-20 counts each time the page loads, so I would appreciate if I could get some tips to speed things up a bit :-) Anyone?

-Try to index the tread and date field in your msgs table.

-Can the date be later then the current date? Why use the BETWEEN expression and not just:

msgs.date >= user.lastlogin

Not sure whether this will help you, but it's worth trying. Good luck!

Thanks, I'll try that. How exactly does indexing work in this case? Do you know some examples i could check out?

Indexing your table won't effect the format of your SQL query. Use the "MSSQL enterprise manager" to index the fields (right click on the tables choose All tasks -> Manage indexes...). Your SQL queries can be the same as before. The SQL server will automaticly use other datastructures to improve the performance of the SELECT query.

For more information about index types, consult some online documentation.