最初にやること

[tetsuzawa]

• たき
  • [x] サービス一覧の確認
  • systemctl list-units --type=service
  • [x] セットアップスクリプトの修正
  • [x] 環境のセットアップ（ツール類のインストール、計測スクリプトの調整、コードのリポジトリ追加）
  • https://www.tetsuzawa.com/docs/ISUCON/init/practice-setup#2-ssh-config%E6%9B%B8%E3%81%8D%E6%8F%9B%E3%81%88%E3%82%8B
  • https://www.tetsuzawa.com/docs/ISUCON/init/setup 参照
  • [ ] 初期ベンチ
• そーだいさん
  • [ ] ベンチマーク実行方法確認
  • [ ] アプリケーションの動作方法確認
• まこぴーさん
  • [ ] インフラ構成確認（cpu, memory, disk, network, swap）
  • [ ] sql一覧確認
    • cat main.go | perl -nal -E 'say "L$.: $1" if /"((?:SELECT|DELETE|UPDATE|INSERT)[^"]*)"/'
    • or ... https://github.com/mazrean/isucrud

[tetsuzawa]

サービス一覧

``` UNIT LOAD ACTIVE SUB DESCRIPTION accounts-daemon.service loaded active running Accounts Service acpid.service loaded active running ACPI event daemon apparmor.service loaded active exited Load AppArmor profiles apport.service loaded active exited LSB: automatic crash report generation atd.service loaded active running Deferred execution scheduler blk-availability.service loaded active exited Availability of block devices cloud-config.service loaded active exited Apply the settings specified in cloud-config cloud-final.service loaded active exited Execute cloud user/final scripts cloud-init-local.service loaded active exited Initial cloud-init job (pre-networking) cloud-init.service loaded active exited Initial cloud-init job (metadata service crawler) console-setup.service loaded active exited Set console font and keymap cron.service loaded active running Regular background program processing daemon dbus.service loaded active running D-Bus System Message Bus finalrd.service loaded active exited Create final runtime dir for shutdown pivot root getty@tty1.service loaded active running Getty on tty1 irqbalance.service loaded active running irqbalance daemon isucholar.go.service loaded active running isucholar.go keyboard-setup.service loaded active exited Set the console keyboard layout kmod-static-nodes.service loaded active exited Create list of static device nodes for the current kernel ● logrotate.service loaded failed failed Rotate log files lvm2-monitor.service loaded active exited Monitoring of LVM2 mirrors, snapshots etc. using dmeventd or progress polling ModemManager.service loaded active running Modem Manager multipathd.service loaded active running Device-Mapper Multipath Device Controller mysql.service loaded active running MySQL Community Server networkd-dispatcher.service loaded active running Dispatcher daemon for systemd-networkd nginx.service loaded active running A high performance web server and a reverse proxy server polkit.service loaded active running Authorization Manager rsyslog.service loaded active running System Logging Service serial-getty@ttyS0.service loaded active running Serial Getty on ttyS0 setvtrgb.service loaded active exited Set console scheme ssh.service loaded active running OpenBSD Secure Shell server systemd-fsck-root.service loaded active exited File System Check on Root Device systemd-journal-flush.service loaded active exited Flush Journal to Persistent Storage systemd-journald.service loaded active running Journal Service systemd-logind.service loaded active running Login Service systemd-modules-load.service loaded active exited Load Kernel Modules systemd-networkd-wait-online.service loaded active exited Wait for Network to be Configured systemd-networkd.service loaded active running Network Service systemd-random-seed.service loaded active exited Load/Save Random Seed systemd-remount-fs.service loaded active exited Remount Root and Kernel File Systems systemd-resolved.service loaded active running Network Name Resolution systemd-sysctl.service loaded active exited Apply Kernel Variables systemd-sysusers.service loaded active exited Create System Users systemd-timesyncd.service loaded active running Network Time Synchronization systemd-tmpfiles-setup-dev.service loaded active exited Create Static Device Nodes in /dev systemd-tmpfiles-setup.service loaded active exited Create Volatile Files and Directories systemd-udev-settle.service loaded active exited udev Wait for Complete Device Initialization systemd-udev-trigger.service loaded active exited udev Coldplug all Devices systemd-udevd.service loaded active running udev Kernel Device Manager systemd-update-utmp.service loaded active exited Update UTMP about System Boot/Shutdown systemd-user-sessions.service loaded active exited Permit User Sessions udisks2.service loaded active running Disk Manager ufw.service loaded active exited Uncomplicated firewall unattended-upgrades.service loaded active running Unattended Upgrades Shutdown user-runtime-dir@1100.service loaded active exited User Runtime Directory /run/user/1100 user@1100.service loaded active running User Manager for UID 1100 ```

[tetsuzawa]

• docker なし
• nginx
• mysql

[mackee]

CPU

2コア

Details

``` processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 85 model name : Intel(R) Xeon(R) Platinum 8275CL CPU @ 3.00GHz stepping : 7 microcode : 0x5003604 cpu MHz : 2999.998 cache size : 36608 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 1 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc cpuid aperfmperf tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves ida arat pku ospke bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs itlb_multihit bogomips : 5999.99 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 85 model name : Intel(R) Xeon(R) Platinum 8275CL CPU @ 3.00GHz stepping : 7 microcode : 0x5003604 cpu MHz : 3605.784 cache size : 36608 KB physical id : 0 siblings : 2 core id : 0 cpu cores : 1 apicid : 1 initial apicid : 1 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc cpuid aperfmperf tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves ida arat pku ospke bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs itlb_multihit bogomips : 5999.99 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: ```

メモリ

4GB, Swapなし

Details

``` MemTotal: 3803064 kB MemFree: 2967432 kB MemAvailable: 3083256 kB Buffers: 19832 kB Cached: 293580 kB SwapCached: 0 kB Active: 86936 kB Inactive: 630208 kB Active(anon): 692 kB Inactive(anon): 416620 kB Active(file): 86244 kB Inactive(file): 213588 kB Unevictable: 22872 kB Mlocked: 18336 kB SwapTotal: 0 kB SwapFree: 0 kB Dirty: 48 kB Writeback: 0 kB AnonPages: 426516 kB Mapped: 97984 kB Shmem: 844 kB KReclaimable: 39036 kB Slab: 59972 kB SReclaimable: 39036 kB SUnreclaim: 20936 kB KernelStack: 2816 kB PageTables: 3912 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 1901532 kB Committed_AS: 1085096 kB VmallocTotal: 34359738367 kB VmallocUsed: 10024 kB VmallocChunk: 0 kB Percpu: 1248 kB HardwareCorrupted: 0 kB AnonHugePages: 0 kB ShmemHugePages: 0 kB ShmemPmdMapped: 0 kB FileHugePages: 0 kB FilePmdMapped: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 0 kB DirectMap4k: 69544 kB DirectMap2M: 3905536 kB DirectMap1G: 0 kB ```

disk

30GB

[mackee]

SQL一覧

DB Graph

node: テーブル 関数

edge: INSERT UPDATE DELETE SELECT 関数呼び出し

graph LR
  classDef table fill:#795548,fill-opacity:0.5
  classDef func fill:#1976D2,fill-opacity:0.5
  func:SubmitAssignment[SubmitAssignment]:::func --> table:classes[classes]:::table
  func:SubmitAssignment[SubmitAssignment]:::func --> table:courses[courses]:::table
  func:SubmitAssignment[SubmitAssignment]:::func --> table:registrations[registrations]:::table
  func:SubmitAssignment[SubmitAssignment]:::func --> table:submissions[submissions]:::table
  func:GetAnnouncementDetail[GetAnnouncementDetail]:::func --> table:announcements[announcements]:::table
  func:GetAnnouncementDetail[GetAnnouncementDetail]:::func --> table:courses[courses]:::table
  func:GetAnnouncementDetail[GetAnnouncementDetail]:::func --> table:registrations[registrations]:::table
  func:GetAnnouncementDetail[GetAnnouncementDetail]:::func --> table:unread_announcements[unread_announcements]:::table
  func:GetAnnouncementDetail[GetAnnouncementDetail]:::func --> table:unread_announcements[unread_announcements]:::table
  func:AddClass[AddClass]:::func --> table:classes[classes]:::table
  func:AddClass[AddClass]:::func --> table:classes[classes]:::table
  func:AddClass[AddClass]:::func --> table:courses[courses]:::table
  func:SearchCourses[SearchCourses]:::func --> table:courses[courses]:::table
  func:SearchCourses[SearchCourses]:::func --> table:users[users]:::table
  func:GetAnnouncementList[GetAnnouncementList]:::func --> table:announcements[announcements]:::table
  func:GetAnnouncementList[GetAnnouncementList]:::func --> table:courses[courses]:::table
  func:GetAnnouncementList[GetAnnouncementList]:::func --> table:registrations[registrations]:::table
  func:GetAnnouncementList[GetAnnouncementList]:::func --> table:unread_announcements[unread_announcements]:::table
  func:SetCourseStatus[SetCourseStatus]:::func --> table:courses[courses]:::table
  func:SetCourseStatus[SetCourseStatus]:::func --> table:courses[courses]:::table
  func:DownloadSubmittedAssignments[DownloadSubmittedAssignments]:::func --> table:classes[classes]:::table
  func:DownloadSubmittedAssignments[DownloadSubmittedAssignments]:::func --> table:classes[classes]:::table
  func:DownloadSubmittedAssignments[DownloadSubmittedAssignments]:::func --> table:submissions[submissions]:::table
  func:DownloadSubmittedAssignments[DownloadSubmittedAssignments]:::func --> table:users[users]:::table
  func:GetGrades[GetGrades]:::func --> table:classes[classes]:::table
  func:GetGrades[GetGrades]:::func --> table:courses[courses]:::table
  func:GetGrades[GetGrades]:::func --> table:registrations[registrations]:::table
  func:GetGrades[GetGrades]:::func --> table:submissions[submissions]:::table
  func:GetGrades[GetGrades]:::func --> table:users[users]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:announcements[announcements]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:announcements[announcements]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:courses[courses]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:registrations[registrations]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:unread_announcements[unread_announcements]:::table
  func:AddAnnouncement[AddAnnouncement]:::func --> table:users[users]:::table
  func:RegisterCourses[RegisterCourses]:::func --> table:courses[courses]:::table
  func:RegisterCourses[RegisterCourses]:::func --> table:registrations[registrations]:::table
  func:RegisterCourses[RegisterCourses]:::func --> table:registrations[registrations]:::table
  func:RegisterScores[RegisterScores]:::func --> table:classes[classes]:::table
  func:RegisterScores[RegisterScores]:::func --> table:submissions[submissions]:::table
  func:RegisterScores[RegisterScores]:::func --> table:users[users]:::table
  func:AddCourse[AddCourse]:::func --> table:courses[courses]:::table
  func:AddCourse[AddCourse]:::func --> table:courses[courses]:::table
  func:GetMe[GetMe]:::func --> table:users[users]:::table
  func:GetRegisteredCourses[GetRegisteredCourses]:::func --> table:courses[courses]:::table
  func:GetRegisteredCourses[GetRegisteredCourses]:::func --> table:registrations[registrations]:::table
  func:GetRegisteredCourses[GetRegisteredCourses]:::func --> table:users[users]:::table
  func:GetClasses[GetClasses]:::func --> table:classes[classes]:::table
  func:GetClasses[GetClasses]:::func --> table:courses[courses]:::table
  func:GetClasses[GetClasses]:::func --> table:submissions[submissions]:::table
  func:GetCourseDetail[GetCourseDetail]:::func --> table:courses[courses]:::table
  func:GetCourseDetail[GetCourseDetail]:::func --> table:users[users]:::table
  func:Login[Login]:::func --> table:users[users]:::table
  linkStyle 0,1,2,4,5,6,7,10,11,12,13,14,15,16,17,18,20,22,23,24,25,26,27,28,29,31,32,34,35,37,38,42,43,44,45,46,47,48,49,50,51,52 stroke:#78909C,stroke-width:2px
  linkStyle 3,9,30,33,36,41 stroke:#CDDC39,stroke-width:2px
  linkStyle 8,19,21,39,40 stroke:#FF9800,stroke-width:2px

Details

```sql select(registrations): select `courses`.* from `registrations` join `courses` on `registrations`.`course_id` = `courses`.`id` where `user_id` = ? select(courses): select `courses`.* from `registrations` join `courses` on `registrations`.`course_id` = `courses`.`id` where `user_id` = ? select(classes): select * from `classes` where `course_id` = ? order by `part` desc select(submissions): select count(*) from `submissions` where `class_id` = ? select(users): select ifnull(sum(`submissions`.`score`), 0) as `total_score` from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `courses`.`id` = ? group by `users`.`id` select(registrations): select ifnull(sum(`submissions`.`score`), 0) as `total_score` from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `courses`.`id` = ? group by `users`.`id` select(courses): select ifnull(sum(`submissions`.`score`), 0) as `total_score` from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `courses`.`id` = ? group by `users`.`id` select(classes): select ifnull(sum(`submissions`.`score`), 0) as `total_score` from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `courses`.`id` = ? group by `users`.`id` select(submissions): select ifnull(sum(`submissions`.`score`), 0) as `total_score` from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `courses`.`id` = ? group by `users`.`id` select(submissions): select `submissions`.`score` from `submissions` where `user_id` = ? and `class_id` = ? select(users): select ifnull(sum(`submissions`.`score` * `courses`.`credit`), 0) / 100 / `credits`.`credits` as `gpa` from `users` join () as `credits` on `credits`.`user_id` = `users`.`id` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` and `courses`.`status` = ? left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `users`.`type` = ? group by `users`.`id` select(registrations): select ifnull(sum(`submissions`.`score` * `courses`.`credit`), 0) / 100 / `credits`.`credits` as `gpa` from `users` join () as `credits` on `credits`.`user_id` = `users`.`id` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` and `courses`.`status` = ? left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `users`.`type` = ? group by `users`.`id` select(courses): select ifnull(sum(`submissions`.`score` * `courses`.`credit`), 0) / 100 / `credits`.`credits` as `gpa` from `users` join () as `credits` on `credits`.`user_id` = `users`.`id` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` and `courses`.`status` = ? left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `users`.`type` = ? group by `users`.`id` select(classes): select ifnull(sum(`submissions`.`score` * `courses`.`credit`), 0) / 100 / `credits`.`credits` as `gpa` from `users` join () as `credits` on `credits`.`user_id` = `users`.`id` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` and `courses`.`status` = ? left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `users`.`type` = ? group by `users`.`id` select(submissions): select ifnull(sum(`submissions`.`score` * `courses`.`credit`), 0) / 100 / `credits`.`credits` as `gpa` from `users` join () as `credits` on `credits`.`user_id` = `users`.`id` join `registrations` on `users`.`id` = `registrations`.`user_id` join `courses` on `registrations`.`course_id` = `courses`.`id` and `courses`.`status` = ? left join `classes` on `courses`.`id` = `classes`.`course_id` left join `submissions` on `users`.`id` = `submissions`.`user_id` and `submissions`.`class_id` = `classes`.`id` where `users`.`type` = ? group by `users`.`id` select(users): select `code` from `users` where `id` = ? select(users): select * from `users` where `code` = ? select(classes): select count(*) from `classes` where `id` = ? for update select(submissions): select `submissions`.`user_id`, `submissions`.`file_name`, `users`.`code` as `user_code` from `submissions` join `users` on `users`.`id` = `submissions`.`user_id` where `class_id` = ? select(users): select `submissions`.`user_id`, `submissions`.`file_name`, `users`.`code` as `user_code` from `submissions` join `users` on `users`.`id` = `submissions`.`user_id` where `class_id` = ? update(classes): update `classes` set `submission_closed` = true where `id` = ? select(announcements): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, `announcements`.`message`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `courses`.`id` = `announcements`.`course_id` join `unread_announcements` on `unread_announcements`.`announcement_id` = `announcements`.`id` where `announcements`.`id` = ? and `unread_announcements`.`user_id` = ? select(courses): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, `announcements`.`message`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `courses`.`id` = `announcements`.`course_id` join `unread_announcements` on `unread_announcements`.`announcement_id` = `announcements`.`id` where `announcements`.`id` = ? and `unread_announcements`.`user_id` = ? select(unread_announcements): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, `announcements`.`message`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `courses`.`id` = `announcements`.`course_id` join `unread_announcements` on `unread_announcements`.`announcement_id` = `announcements`.`id` where `announcements`.`id` = ? and `unread_announcements`.`user_id` = ? select(registrations): select count(*) from `registrations` where `course_id` = ? and `user_id` = ? update(unread_announcements): update `unread_announcements` set `is_deleted` = true where `announcement_id` = ? and `user_id` = ? select(courses): select count(*) from `courses` where `id` = ? insert(announcements): insert into `announcements` (`id`, `course_id`, `title`, `message`) values (?, ?, ?, ?) select(users): select `users`.* from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` where `registrations`.`course_id` = ? select(registrations): select `users`.* from `users` join `registrations` on `users`.`id` = `registrations`.`user_id` where `registrations`.`course_id` = ? select(announcements): select * from `announcements` where `id` = ? insert(unread_announcements): insert into `unread_announcements` (`announcement_id`, `user_id`) values (?, ?) select(classes): select `submission_closed` from `classes` where `id` = ? for share update(submissions): update `submissions` join `users` on `users`.`id` = `submissions`.`user_id` set `score` = ? where `users`.`code` = ? and `class_id` = ? update(users): update `submissions` join `users` on `users`.`id` = `submissions`.`user_id` set `score` = ? where `users`.`code` = ? and `class_id` = ? select(courses): select `courses`.*, `users`.`name` as `teacher` from `courses` join `users` on `courses`.`teacher_id` = `users`.`id` where `courses`.`id` = ? select(users): select `courses`.*, `users`.`name` as `teacher` from `courses` join `users` on `courses`.`teacher_id` = `users`.`id` where `courses`.`id` = ? select(courses): select `courses`.* from `courses` join `registrations` on `courses`.`id` = `registrations`.`course_id` where `courses`.`status` != ? and `registrations`.`user_id` = ? select(registrations): select `courses`.* from `courses` join `registrations` on `courses`.`id` = `registrations`.`course_id` where `courses`.`status` != ? and `registrations`.`user_id` = ? select(users): select * from `users` where `id` = ? insert(courses): insert into `courses` (`id`, `code`, `type`, `name`, `description`, `credit`, `period`, `day_of_week`, `teacher_id`, `keywords`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) select(courses): select * from `courses` where `code` = ? select(courses): select `status` from `courses` where `id` = ? for share select(registrations): select count(*) from `registrations` where `user_id` = ? and `course_id` = ? select(classes): select `submission_closed` from `classes` where `id` = ? for share insert(submissions): insert into `submissions` (`user_id`, `class_id`, `file_name`) values (?, ?, ?) on duplicate key update `file_name` = values(`file_name`) select(courses): select * from `courses` where `id` = ? for share select(courses): select `courses`.* from `courses` join `registrations` on `courses`.`id` = `registrations`.`course_id` where `courses`.`status` != ? and `registrations`.`user_id` = ? select(registrations): select `courses`.* from `courses` join `registrations` on `courses`.`id` = `registrations`.`course_id` where `courses`.`status` != ? and `registrations`.`user_id` = ? select(registrations): select count(*) from `registrations` where `course_id` = ? and `user_id` = ? insert(registrations): insert into `registrations` (`course_id`, `user_id`) values (?, ?) on duplicate key update `course_id` = values(`course_id`), `user_id` = values(`user_id`) select(courses): select * from `courses` where `id` = ? for share insert(classes): insert into `classes` (`id`, `course_id`, `part`, `title`, `description`) values (?, ?, ?, ?, ?) select(classes): select * from `classes` where `course_id` = ? and `part` = ? select(courses): select `courses`.*, `users`.`name` as `teacher` from `courses` join `users` on `courses`.`teacher_id` = `users`.`id` where 1=1 select(users): select `courses`.*, `users`.`name` as `teacher` from `courses` join `users` on `courses`.`teacher_id` = `users`.`id` where 1=1 select(courses): select count(*) from `courses` where `id` = ? for update update(courses): update `courses` set `status` = ? where `id` = ? select(courses): select count(*) from `courses` where `id` = ? select(classes): select `classes`.*, `submissions`.`user_id` is not null as `submitted` from `classes` left join `submissions` on `classes`.`id` = `submissions`.`class_id` and `submissions`.`user_id` = ? where `classes`.`course_id` = ? order by `classes`.`part` select(submissions): select `classes`.*, `submissions`.`user_id` is not null as `submitted` from `classes` left join `submissions` on `classes`.`id` = `submissions`.`class_id` and `submissions`.`user_id` = ? where `classes`.`course_id` = ? order by `classes`.`part` select(announcements): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `announcements`.`course_id` = `courses`.`id` join `registrations` on `courses`.`id` = `registrations`.`course_id` join `unread_announcements` on `announcements`.`id` = `unread_announcements`.`announcement_id` where 1=1 select(courses): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `announcements`.`course_id` = `courses`.`id` join `registrations` on `courses`.`id` = `registrations`.`course_id` join `unread_announcements` on `announcements`.`id` = `unread_announcements`.`announcement_id` where 1=1 select(registrations): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `announcements`.`course_id` = `courses`.`id` join `registrations` on `courses`.`id` = `registrations`.`course_id` join `unread_announcements` on `announcements`.`id` = `unread_announcements`.`announcement_id` where 1=1 select(unread_announcements): select `announcements`.`id`, `courses`.`id` as `course_id`, `courses`.`name` as `course_name`, `announcements`.`title`, not `unread_announcements`.`is_deleted` as `unread` from `announcements` join `courses` on `announcements`.`course_id` = `courses`.`id` join `registrations` on `courses`.`id` = `registrations`.`course_id` join `unread_announcements` on `announcements`.`id` = `unread_announcements`.`announcement_id` where 1=1 select(unread_announcements): select count(*) from `unread_announcements` where `user_id` = ? and not `is_deleted` ```