mysql - 如何在 where 子句中优化带有 OR 的计数行的 sql 查询？

Question

我编写了一个 SQL 查询来计算行数。我在 WHERE 子句中使用 IN 和 OR。当前查询需要很长时间（大约 10 秒）才能执行。请帮我优化 SQL 查询。我正在分享我的代码：

  COUNT(*)
FROM
  booking_status_journey bs
  INNER JOIN booking_indonesia b ON b.id = bs.booking
WHERE
  bs.hid IN (
    4,
    5,
    6,
    7,
    11,
    14,
    16,
    17,
    18,
    19,
    23,
    24,
    25,
    26
  )
  AND (
    (
      UNIX_TIMESTAMP(STR_TO_DATE(bs.picked_up_fromwh, '%d/%m/%Y')) <= UNIX_TIMESTAMP('2020-10-15')
      AND UNIX_TIMESTAMP(STR_TO_DATE(bs.picked_up_fromwh, '%d/%m/%Y')) >= UNIX_TIMESTAMP(DATE_SUB('2020-10-15', INTERVAL 5 DAY))
      AND b.no_show_count = 0
      AND bs.rabbit_id1 IS NOT NULL
      AND bs.parcel_picked1 IS NULL
      AND bs.start_delivery1 IS NULL
      AND bs.arrived_at_drop_off1 IS NULL
      AND bs.delivered IS NULL
    )
    OR (
      UNIX_TIMESTAMP(STR_TO_DATE(bs.picked_up_fromwh, '%d/%m/%Y')) <= UNIX_TIMESTAMP('2020-10-15')
      AND UNIX_TIMESTAMP(STR_TO_DATE(bs.picked_up_fromwh, '%d/%m/%Y')) >= UNIX_TIMESTAMP(DATE_SUB('2020-10-15', INTERVAL 5 DAY))
      AND b.no_show_count = 1
      AND bs.rabbit_id2 IS NOT NULL
      AND bs.parcel_picked2 IS NULL
      AND bs.no_show1 IS NOT NULL
      AND bs.start_delivery2 IS NULL
      AND bs.arrived_at_drop_off2 IS NULL
      AND bs.delivered IS NULL
      AND bs.failed IS NULL
      AND bs.returned_after_failed IS NULL
      AND bs.returned_after_no_show1 IS NULL
      AND bs.returned_towh IS NULL
    )
  )```



  [1]: https://i.stack.imgur.com/mPDTG.png

标签： mysqlsql