1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query stalls, never finishes

Discussion in 'MySQL' started by sarahk, Aug 15, 2017.

  1. #1
    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.
     
    sarahk, Aug 15, 2017 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    1. What does your SQL error log tell you?
    2. You're using backticks two places for the Finances.startdate, but not on the last one. Far fetched, but maybe?
     
    PoPSiCLe, Aug 15, 2017 IP
    sarahk likes this.
  3. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #3
    How many records in your tables? Generally speaking, subqueries should be avoided if possible... The internal optimizer that handles subqueries is pretty stupid.
     
    digitalpoint, Aug 16, 2017 IP
    sarahk likes this.
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #4
    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 :)
     
    sarahk, Aug 16, 2017 IP
  5. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #5
    Have you run the query through the SQL ANALYZE statement?
     
    digitalpoint, Aug 16, 2017 IP
    sarahk likes this.
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #6
    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.
     
    sarahk, Aug 16, 2017 IP
    digitalpoint likes this.
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #7
    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):
     
    sarahk, Aug 19, 2017 IP
  8. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #8
    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.
     
    digitalpoint, Aug 21, 2017 IP
    sarahk likes this.
  9. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #9
    I hadn't used the query to make a temp table thing before - that is pretty slick. Essentially creating a view.
     
    sarahk, Aug 21, 2017 IP