List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 24 2011 11:05pm
Subject:RE: Within-group aggregate query help please - customers and latest subscription row
View as plain text  
A kind (and shy) soul replied to me off list and suggested this solution,
however,
this takes 28 seconds (that's for a single customer_id, so this is not going
to scale). 
Got any other suggestions? :-)

SELECT 
        c.customer_id,
        c.email,
        c.name,
        c.username,
        s.subscription_id,
        s.`date`
FROM
        customers AS c 
        INNER JOIN customers_subscriptions AS s 
                ON c.customer_id = s.customer_id 
        INNER JOIN 
                (SELECT 
                        MAX(`date`) AS LastDate,
                        customer_id 
                FROM
                        customers_subscriptions AS cs 
                GROUP BY customer_id) AS `x`
                ON s.customer_id = x.customer_id 
                AND s.date = x.LastDate 
WHERE c.customer_id = 7;

There are 781,270 customers (nearly 1 million) and  1,018,092
customer_subscriptions.

Our tables have many indexes on pretty much every column and for sure the
ones we use here.

EXPLAIN says:

    id  select_type  table       type    possible_keys     key
key_len  ref        rows  Extra                          
------  -----------  ----------  ------  ----------------  -----------
-------  ------  -------  -------------------------------
     1  PRIMARY      c           const   PRIMARY           PRIMARY      4
const         1                                 
     1  PRIMARY      s           ref     date,customer_id  customer_id  4
const         2                                 
     1  PRIMARY      <derived2>  ALL     (NULL)            (NULL)
(NULL)   (NULL)   781265  Using where                    
     2  DERIVED      cs          ALL     (NULL)            (NULL)
(NULL)   (NULL)  1018092  Using temporary; Using filesort

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Monday, October 24, 2011 1:46 PM
> To: mysql@stripped
> Subject: Within-group aggregate query help please - customers and latest
> subscription row
> 
> I know this is a common problem, and I've been struggling with it for a
full
> day now but I can't get it.
> 
> I also tried a few sites for examples:
> http://www.artfulsoftware.com/infotree/queries.php#101
>
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
> -problem-10210.html
> 
> Anyways, pretty standard situation:
> 
> CREATE TABLE `customers` (
>   `customer_id` int(10) unsigned NOT NULL auto_increment,
>   `email` varchar(64) NOT NULL default '',
>   `name` varchar(128) NOT NULL default '',
>   `username` varchar(32) NOT NULL,
> 	...
> );
> 
> CREATE TABLE `customers_subscriptions` (
>   `subscription_id` bigint(12) unsigned NOT NULL default '0',
>   `customer_id` int(10) unsigned NOT NULL default '0',
>   `date` date NOT NULL default '0000-00-00',
>   ...
> );
> 
> I want to show a table where I list out the ID, email, username, and LAST
> SUBSCRIPTION.
> 
> I need this data in TWO ways:
> 
> The FIRST way, is with a query JOINing the two tables so that I can easily
> display that HTML table mentioned. That is ALL customers and the latest
> subscription they have.
> 
> The SECOND way is when I drill into the customer, I already know the
> customer_id and so don't need to JOIN with that table, I just want to get
> the proper row from the customers_subscriptions table itself.
> 
> SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
> `date` DESC;
> 
> subscription_id  processor  customer_id  date
> ---------------  ---------  -----------  ----------
>       134126370  chargem              7  2005-08-04
>      1035167192  billme               7  2004-02-08
> 
> SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
> GROUP BY customer_id;
> 
> gives me 2005-08-04 obviously, but as you all know, mySQL completely takes
a
> crap on your face when you try what would seem to be the right query:
> 
> SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
> customer_id = 7 GROUP BY customer_id;
> 
> subscription_id  MAX(`date`)
> ---------------  -----------
>      1035167192  2005-08-04
> 
> Notice how I have the correct DATE, but the wrong subscription_id.
> 
> In the example web sites above, they seem to deal more with finding the
> MAX(subscription_id), which in my case will not work.
> 
> I need the max DATE and the corresponding row (with matching
> subscription_id).
> 
> Thanks,
> 
> d

Thread
Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent24 Oct
  • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
    • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
      • RE: Within-group aggregate query help please - customers andlatest subscription rowhsv25 Oct