top3 with or without "having" in query

#1 (**permalink**) 02-28-2008, 10:27 AM

Which is faster and easier ?

I have a table with messages from users. I want to fetch top3 users
whom have sent most messages and more than X (X being a number).
User data, such as name and id are stored in another table.

A) add a new field in table_users to hold the total number of messages
sent (like "messages_total"), and then when querying for top3 it will
look something like this: "SELECT id.table_users, name.table_users,
COUNT(*.table_messages) FROM table_users, table_messages WHERE
messages_total.table_users >= X ORDER BY DESC LIMIT 3"
This should also allow users to delete messages while keeping their
total count.

B) use the term 'having' in the query to avoid having the extra field
("messages_total") in the user table. I think this is how the query
will look like: "SELECT id.table_users, name.table_users,
COUNT(*.table_messages) FROM table_users, table_messages HAVING
messages_total.table_users >= X ORDER BY DESC LIMIT 3"

C) something else ?

#2 (**permalink**) 02-28-2008, 10:27 AM

Kim Slot wrote:
> Which is faster and easier ?
>
> I have a table with messages from users. I want to fetch top3 users
> whom have sent most messages and more than X (X being a number).
> User data, such as name and id are stored in another table.
>
> A) add a new field in table_users to hold the total number of messages
> sent (like "messages_total"), and then when querying for top3 it will
> look something like this: "SELECT id.table_users, name.table_users,
> COUNT(*.table_messages) FROM table_users, table_messages WHERE
> messages_total.table_users >= X ORDER BY DESC LIMIT 3"
> This should also allow users to delete messages while keeping their
> total count.
>
> B) use the term 'having' in the query to avoid having the extra field
> ("messages_total") in the user table. I think this is how the query
> will look like: "SELECT id.table_users, name.table_users,
> COUNT(*.table_messages) FROM table_users, table_messages HAVING
> messages_total.table_users >= X ORDER BY DESC LIMIT 3"
>
> C) something else ?

Easier? That is down to which one you find easier!

Faster? Time both of them and you will find out!

#3 (**permalink**) 02-28-2008, 10:27 AM

On Sep 21, 1:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Kim Slot wrote:
> > Which is faster and easier ?
>
> > I have a table with messages from users. I want to fetch top3 users
> > whom have sent most messages and more than X (X being a number).
> > User data, such as name and id are stored in another table.
>
> > A) add a new field in table_users to hold the total number of messages
> > sent (like "messages_total"), and then when querying for top3 it will
> > look something like this: "SELECT id.table_users, name.table_users,
> > COUNT(*.table_messages) FROM table_users, table_messages WHERE
> > messages_total.table_users >= X ORDER BY DESC LIMIT 3"
> > This should also allow users to delete messages while keeping their
> > total count.
>
> > B) use the term 'having' in the query to avoid having the extra field
> > ("messages_total") in the user table. I think this is how the query
> > will look like: "SELECT id.table_users, name.table_users,
> > COUNT(*.table_messages) FROM table_users, table_messages HAVING
> > messages_total.table_users >= X ORDER BY DESC LIMIT 3"
>
> > C) something else ?
>
> Easier? That is down to which one you find easier!
>
> Faster? Time both of them and you will find out!

That clearly wasnt the answer I hoped for.
If I could test it, then I would have done so instead of asking.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode