|
|
Posted by Jerry Stuckle on 11/29/07 17:05
Toby A Inkster wrote:
> 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.)
>
comp.databases.mysql has a lot on replacing subqueries with JOINs.
Pretty much any subquery can be replaced with a JOIN.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|