Query stalls, never finishes

So I'm guessing I've got something wrong and I'm just not seeing it. Another set of eyes would be appreciated

SELECT
   DISTINCT
    Finance.`startdate`
   ,
   (SELECT
      COUNT(Fin.id)
   FROM
      finances AS Fin
      , memberships AS Membership
   WHERE Fin.membership_id = Membership.id
      AND Membership.softdelete = 'N'
      AND Membership.association_id = 4
      AND Finance.startdate >= Fin.startdate
      AND Finance.startdate <= Fin.enddate)
FROM
   finances AS Finance
WHERE Finance.`startdate` < NOW()
ORDER BY Finance.startdate DESC
LIMIT 20

Code (markup):

The main query is just going to a table and get a list of dates - the first of every month

The subquery takes that month and goes to find out how many finance records were "active" on that month - typically a finance record would be active for a year, but it does differ. That's why you have the startdate and enddate comparisons.

It started life with joins but this old school table stuff should work too.

 

How many records in your tables? Generally speaking, subqueries should be avoided if possible... The internal optimizer that handles subqueries is pretty stupid.

 

finances is pretty big by our standards but piddly really - 42k

The query is a one off, might turn into a report, but will be run infrequently. I'll tidy up the table names, back ticks etc when/if that happens

 

Have you run the query through the SQL ANALYZE statement?

 

I don't have the management tool, I use SQLyog and it generally does what I need. I ended up adding a between to the date clause in the top query and it came right, slow but it worked.

 

I've got this just about finished and it runs in just over a second which is pretty good, earlier iterations took nearly 2 minutes. Where I had subqueries in the column selection I've moved them to the "from" section. Still using old school table joining but it gets the job done.

SELECT
   `Finance`.`startdate`
   , COUNT(`F2`.`id`) AS total
   , SUM(
      CASE
         WHEN Finance.startdate = F2.startdate
         THEN
         CASE
            WHEN F2.nrstatus = 0
            THEN 1
            ELSE 0
         END
         ELSE 0
      END
   ) AS newmembers
   , SUM(
      CASE
         WHEN Finance.startdate = F2.startdate
         THEN
         CASE
            WHEN F2.nrstatus = 0
            THEN 0
            ELSE 1
         END
         ELSE 0
      END
   ) AS renewmembers
FROM
   (SELECT DISTINCT
      F.startdate
   FROM
      finances AS F
   WHERE F.startdate BETWEEN DATE_ADD(NOW(), INTERVAL - 2 YEAR)
      AND NOW()
   ORDER BY F.startdate DESC
   LIMIT 20) AS Finance
   , memberships AS Membership
   ,
   (SELECT
      startdate, enddate
      , F1.id
      , F1.membership_id
      ,
      (SELECT
         COUNT(Fcheck.id)
      FROM
         finances AS Fcheck
      WHERE F1.startdate > Fcheck.startdate
         AND F1.`membership_id` = Fcheck.`membership_id`) AS nrstatus
   FROM
      finances AS F1) AS F2
WHERE `F2`.`membership_id` = `Membership`.`id`
   AND Membership.softdelete = 'N'
   AND Membership.association_id = 4
   AND Finance.startdate >= F2.startdate
   AND Finance.startdate <= F2.enddate
GROUP BY Finance.startdate
ORDER BY Finance.startdate DESC

Code (markup):

 

I know it doesn't help in your situation, but anytime you can avoid subqueries, do it. Like if you were looking up a static value with a subquery, it's better to do the query you do with your subquery, get the value in your application, then apply that value statically to the query. You end up with 2 queries instead of 1, but say you had 100,000 records in the table you are querying, internally MySQL could be doing 100,000 duplicate queries (one for each row), so internally MySQL could be doing 100,001 queries.

Just depends on the subquery of course and not always possible to avoid, but any time it's possible to avoid them, do it.

 

I hadn't used the query to make a temp table thing before - that is pretty slick. Essentially creating a view.