Hi.
On Wed 2002-06-05 at 13:37:22 -0500, [EMAIL PROTECTED] wrote:
[...]
> It selects a list of all members from the database referred by a certain
> member. However, the query needs to be modified to also select the
> number of people that person has referred. I planned to just send
> another query looping through the results, but if a member has referred
> 100 people, that's 100 queries, and probably not the best idea?
That depends on your data and indexes. Sometimes a query becomes too
complex for the optimzer of the RDMBS to do a good job. In those cases
(and if the speed difference is actually relevant), it may be faster
to do the non-pretty approch.
That said, I would be suprised, if in your case the 101 queries would
be faster than the one little bit more complex one (at least with the
right indexes).
> So to do this query, do I need to left join the table to itself?
> Maybe something like:
>
> SELECT m1.username, m1.first_name, m1.email, DATE_FORMAT(m1.signup_date,
> '%b %e, %Y') AS signup_date, COUNT(m2.id) AS count FROM members AS m1
> LEFT JOIN members AS m2 ON m1.referer = m2.id WHERE m1.referer = 4 GROUP
> BY m1.referer
>
> Is that even close? heh
Looks perfect to me. Any reason you are not satisfied with this? Does
the result (you did try this out, didn't you? ;-) not match your
expectations?
Recommend index is of course, INDEX(referer). If the selected values
are only a small portion of the tables, a combined index, namely
INDEX(referer,username,first_name,email,signup_date), would help to
prevent touching the data file at all (only using index file), but
that only becomes relevant, if you are really observing the query
being to slow. Especially, since the latter index may get quite big.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
