Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Railsでのクエリ改善の話

 Railsでのクエリ改善の話

ANDPAD TechLive #3 プロダクトの成長と共に歩むRails開発改善の歴史と未来での発表資料です。
https://andpad.connpass.com/event/204747

Ryunosuke Sato

March 02, 2021
Tweet

More Decks by Ryunosuke Sato

Other Decks in Technology

Transcript

  1. σʔλʹ͍ͭͯ 1 n 1 1 n n ඇৗʹ਺͕ଟ͍ .BYϢʔβʔʷҊ݅ 6TFS

    0SEFS /PUJpDBUJPO 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
  2. ·ͣ͸ݱঢ় Ҋ݅ͱ௨஌Λ+0*/͍ͯͨ͠ SELECT orders.*, SUM(IF(notifications.id IS NULL, 0, 1)) AS

    unread_count FROM `orders` LEFT JOIN notifications ON notifications.order_id = orders.id AND notifications.user_id = ? AND notifications.state = 0 WHERE `orders`.`deleted_at` IS NULL GROUP BY orders.id
  3. ະಡ਺ͷදࣔ 6TFS 0SEFS /PUJpDBUJPO 5FBN 1 n n 1 1

    n ඇৗʹ਺͕ଟ͍ .BYϢʔβʔʷҊ݅ ͜͜Λࢀর͍ͯͨ͠ͷΛ΍Ίͯ… Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
  4. ະಡ਺ͷදࣔ 1 n 1 1 1. ʮະಡ਺ʯΛΧϥϜͱͯ͠௥Ճ͢Δ 2. σʔλߋ৽࣌ʹɺ ʮະಡ਺ʯΛॻ͖ࠐΉ

    n ͪ͜ΒΛࢀরͰ͖Δ͔ /PUJpDBUJPO 6TFS 0SEFS 5FBN Ϣʔβʔ Ҋ݅ Ҋ݅ͷॴଐ ௨஌
  5. ͍ͬͨΜͷΰʔϧ ʲ#FGPSFʳ SELECT orders.*, SUM(IF(notifications.id IS NULL, 0, 1)) AS

    unread_count FROM `orders` LEFT JOIN notifications ON notifications.order_id = orders.id AND notifications.user_id = ? AND notifications.state = 0 WHERE `orders`.`deleted_at` IS NULL GROUP BY orders.id
  6. ʲ"GUFSʳ SELECT orders.*, CASE WHEN t.notifications_yet_count IS NULL THEN 0

    ELSE t.notifications_yet_count END AS unread_count FROM `orders` LEFT JOIN teams AS t ON t.order_id = orders.id AND t.deleted_at IS NULL AND t.user_id = ? WHERE `orders`.`deleted_at` IS NULL ͍ͬͨΜͷΰʔϧ ˠ+0*/͢ΔςʔϒϧͷϨίʔυྔ͕࡟ݮ͞Εͨ