-- Optimization indexes for v_user on MariaDB 10.4
-- Safe to run as standalone SQL. Review comments before executing.
-- Note: If an index already exists, the ADD INDEX will fail. In that case,
--       either skip that statement or DROP the existing index first.

-- mst_user: main filtering and ordering for v_user
ALTER TABLE mst_user
  ADD INDEX idx_mst_user_grp_del_user (group_uuid, delete_flg, user_id);

-- Helpful for joins if not already primary/unique
ALTER TABLE mst_user
  ADD INDEX idx_mst_user_uuid (user_uuid);

-- mst_user_admin: used when filtering by admin and joining to mst_user
ALTER TABLE mst_user_admin
  ADD INDEX idx_mst_user_admin_admin_user (admin_uuid, user_uuid);

-- If admin filtering is rare, this join-oriented index can also help
-- (Uncomment if needed)
-- ALTER TABLE mst_user_admin
--   ADD INDEX idx_mst_user_admin_user (user_uuid);

-- tbl_report: left join on current day, delete_flg=0
ALTER TABLE tbl_report
  ADD INDEX idx_tbl_report_user_date_del (insert_uuid, report_date, delete_flg);

-- tbl_contact: basis for v_contact and v_contact_max (latest per user)
-- First, a generic index to support join on (insert_uuid, insert_date)
ALTER TABLE tbl_contact
  ADD INDEX idx_contact_user_date (insert_uuid, insert_date);

-- Optional: include contact_div to pre-filter (< 4) on index
-- (Uncomment if needed and beneficial per EXPLAIN)
-- ALTER TABLE tbl_contact
--   ADD INDEX idx_contact_user_div_date (insert_uuid, contact_div, insert_date);

-- tbl_chat: supports v_chat_unread (distinct user_uuid where admin_chat_div=1 and delete_flg=0)
ALTER TABLE tbl_chat
  ADD INDEX idx_chat_admin_del_user (admin_chat_div, delete_flg, user_uuid);

-- Alternative shape prioritizing join by user_uuid (uncomment if needed)
-- ALTER TABLE tbl_chat
--   ADD INDEX idx_chat_user_admin_del (user_uuid, admin_chat_div, delete_flg);

-- Refresh statistics after index creation
ANALYZE TABLE mst_user, mst_user_admin, tbl_report, tbl_contact, tbl_chat;

-- Verification example (run separately):
-- EXPLAIN SELECT user_uuid, user_id, user_div, user_name, sex_div, birthday, user_area,
--                 contact_uuid, contact_div, contact_date, confirm_div,
--                 report_uuid, charge_comment, chat_user_uuid
--   FROM v_user
--  WHERE group_uuid = '...'
--    AND delete_flg = 0
--  ORDER BY user_id;

