|
|
Posted by Toby A Inkster on 11/29/07 13:16
Rik Wasmus wrote:
> (LEFT) JOINS are usually faster (and more portable).
If I understand the structure correctly, then the table consists of
basically these columns:
user_id
product_id
access_date
With perhaps some other columns (IP address, User-Agent, etc) but we're
ignoring them for now. Multiple accesses to a product by a single user are
recorded as multiple rows in the table. If that is indeed the case, then I
don't see how the requested information (for each product, a list of users
sorted in order of last access time, and indicating a count of accesses)
can be produced without using a subquery.
Joins will of course come in handy too, as you probably don't just want to
include the user's ID and product ID, but want to include their names and
perhaps some other info too:
SELECT
p.product_id,
p.product_name,
u.user_id,
u.fullname,
u.email_address,
sub.last_access,
sub.count_access
FROM (
SELECT
user_id,
product_id,
MAX(access_date) AS last_access,
COUNT(*) AS count_access
FROM product_accesses
GROUP BY user_id, product_id
) AS sub
LEFT JOIN products p
ON sub.product_id=p.product_id
LEFT JOIN users u
ON sub.user_id=u.user_id
ORDER BY sub.product_id, sub.last_access DESC
Yes, joins are fast, but if the information can't be extracted in a single
query with joins, then using a subquery or two is still likely to be
significantly faster than just pulling all the data into PHP and doing the
processing there, or (heaven forbid!) pulling off part of the data, and
then performing a second query for each row of the original dataset!
And regarding portability, any database that doesn't support subqueries
belongs on a scrapheap. (Or if it's got its original packaging in good
condition, maybe a museum of antiques and curiosities.)
--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 19:45.]
[Now Playing: Ryan Adams - Love Is Hell]
Sharing Music with Apple iTunes
http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/
[Back to original message]
|