Should we set the values in SQL?



  • There is such a request:

    SELECT
      sum(money)
    FROM
      `accounts`
    WHERE
       date >= (CURDATE()-1) AND date < CURDATE() AND type IN (101,225)
    

    There is a need for a change in type, several requests, such as:

    SELECT
      sum(money)
    FROM
      `accounts`
    WHERE
      date >= (CURDATE()-1) AND date < CURDATE() AND type IN (353,448)
    ...
    

    How can such a request be optimized?



  • Try your data version with JOIN. At least reduces the number of motions to the disk (compared to UNION ALL)

    SELECT
      sum(money)
    FROM
      `accounts`
      JOIN (
        SELECT 101 as param_type
        UNION ALL SELECT 225
        UNION ALL SELECT 353
        UNION ALL SELECT 448
      ) T ON param_type = type
    WHERE
      date >= (CURDATE()-1) AND date < CURDATE()
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2