SQL: キャップストーンプロジェクト：ブログシステム

[users] 1 ──── N [articles] N ──── N [tags]
   │                  │
   │                  │ 1
   │                  │
   │                  N
   │             [comments]
   │                  │
   N                  │ （自己参照）
   │                  │
[users] 1 ──── N [comments]

[categories] 1 ──── N [articles]
     │
     └── 1 （自己参照、parent_id）

-- =============================================
-- ブログシステムデータベーススキーマ
-- 文字セット：utf8mb4（emojiと多言語をサポート）
-- ストレージエンジン：InnoDB（トランザクションと外部キーをサポート）
-- =============================================

-- データベース作成
CREATE DATABASE IF NOT EXISTS blog_system
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

USE blog_system;

-- ----------------------------
-- 1. ユーザーテーブル
-- ----------------------------
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'ユーザーID',
    username VARCHAR(50) NOT NULL COMMENT 'ユーザー名',
    email VARCHAR(100) NOT NULL COMMENT 'メールアドレス',
    password_hash VARCHAR(255) NOT NULL COMMENT 'パスワードハッシュ',
    nickname VARCHAR(50) COMMENT 'ニックネーム',
    avatar_url VARCHAR(500) COMMENT 'アバターURL',
    bio VARCHAR(500) COMMENT '自己紹介',
    website VARCHAR(200) COMMENT '個人サイト',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'ステータス: 1-有効 0-無効 2-認証待ち',
    role VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT '役割: user/author/admin',
    article_count INT NOT NULL DEFAULT 0 COMMENT '記事数（非正規化）',
    follower_count INT NOT NULL DEFAULT 0 COMMENT 'フォロワー数（非正規化）',
    last_login_at DATETIME COMMENT '最終ログイン日時',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '論理削除: 0-通常 1-削除済',
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ユーザー テーブル';

-- ----------------------------
-- 2. ユーザーフォロー関係テーブル（多対多）
-- ----------------------------
CREATE TABLE user_follows (
    follower_id BIGINT NOT NULL COMMENT 'フォロワーID',
    following_id BIGINT NOT NULL COMMENT 'フォロー先ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'フォロー日時',
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following_id (following_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザーフォロー関係テーブル';

-- ----------------------------
-- 3. カテゴリテーブル（階層カテゴリ対応）
-- ----------------------------
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'カテゴリID',
    name VARCHAR(50) NOT NULL COMMENT 'カテゴリ名',
    slug VARCHAR(100) NOT NULL COMMENT 'URLスラッグ',
    description VARCHAR(200) COMMENT 'カテゴリ説明',
    parent_id BIGINT DEFAULT NULL COMMENT '親カテゴリID、NULLはトップレベル',
    sort_order INT NOT NULL DEFAULT 0 COMMENT 'ソート順',
    article_count INT NOT NULL DEFAULT 0 COMMENT '記事数（非正規化）',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_slug (slug),
    INDEX idx_parent_id (parent_id),
    INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='カテゴリテーブル';

-- ----------------------------
-- 4. タグテーブル
-- ----------------------------
CREATE TABLE tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'タグID',
    name VARCHAR(50) NOT NULL COMMENT 'タグ名',
    slug VARCHAR(100) NOT NULL COMMENT 'URLスラッグ',
    article_count INT NOT NULL DEFAULT 0 COMMENT '記事数（非正規化）',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_name (name),
    UNIQUE KEY uk_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='タグテーブル';

-- ----------------------------
-- 5. 記事テーブル
-- ----------------------------
CREATE TABLE articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '記事ID',
    user_id BIGINT NOT NULL COMMENT '著者ID',
    category_id BIGINT COMMENT 'カテゴリID',
    title VARCHAR(200) NOT NULL COMMENT 'タイトル',
    slug VARCHAR(200) NOT NULL COMMENT 'URLスラッグ',
    summary VARCHAR(500) COMMENT '要約',
    content LONGTEXT NOT NULL COMMENT '本文',
    cover_image_url VARCHAR(500) COMMENT 'カバー画像',
    status TINYINT NOT NULL DEFAULT 0 COMMENT 'ステータス: 0-下書き 1-公開 2-非公開',
    is_pinned TINYINT NOT NULL DEFAULT 0 COMMENT 'ピン留め: 0-いいえ 1-はい',
    view_count INT NOT NULL DEFAULT 0 COMMENT '閲覧数',
    like_count INT NOT NULL DEFAULT 0 COMMENT 'いいね数',
    comment_count INT NOT NULL DEFAULT 0 COMMENT 'コメント数（非正規化）',
    published_at DATETIME COMMENT '公開日時',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '論理削除: 0-通常 1-削除済',
    UNIQUE KEY uk_slug (slug),
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_published (status, published_at DESC),
    INDEX idx_is_pinned (is_pinned),
    INDEX idx_created_at (created_at DESC),
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='記事テーブル';

-- ----------------------------
-- 6. 記事-タグ関連テーブル（多対多）
-- ----------------------------
CREATE TABLE article_tag (
    article_id BIGINT NOT NULL COMMENT '記事ID',
    tag_id BIGINT NOT NULL COMMENT 'タグID',
    PRIMARY KEY (article_id, tag_id),
    INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='記事-タグ関連テーブル';

-- ----------------------------
-- 7. コメントテーブル（ネスト返信対応）
-- ----------------------------
CREATE TABLE comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'コメントID',
    article_id BIGINT NOT NULL COMMENT '記事ID',
    user_id BIGINT NOT NULL COMMENT 'コメント投稿者ID',
    parent_id BIGINT DEFAULT NULL COMMENT '親コメントID、NULLはトップレベル',
    reply_to_user_id BIGINT DEFAULT NULL COMMENT '返信先ユーザーID',
    content TEXT NOT NULL COMMENT 'コメント内容',
    like_count INT NOT NULL DEFAULT 0 COMMENT 'いいね数',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'ステータス: 0-承認待ち 1-承認済 2-却下',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '論理削除',
    INDEX idx_article_id (article_id),
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_created_at (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='コメントテーブル';

-- ----------------------------
-- 8. 記事いいねテーブル
-- ----------------------------
CREATE TABLE article_likes (
    user_id BIGINT NOT NULL COMMENT 'ユーザーID',
    article_id BIGINT NOT NULL COMMENT '記事ID',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, article_id),
    INDEX idx_article_id (article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='記事いいねテーブル';

-- 新規ユーザー登録
INSERT INTO users (username, email, password_hash, nickname)
VALUES ('zhangsan', 'zhangsan@example.com', SHA2('mypassword123', 256), '張三');

-- ユーザーログイン（ユーザー名でクエリ）
SELECT id, username, nickname, avatar_url, role
FROM users
WHERE username = 'zhangsan' AND password_hash = SHA2('mypassword123', 256)
    AND status = 1 AND is_deleted = 0;

-- プロフィール更新
UPDATE users
SET nickname = '張三丰',
    bio = 'プログラミングと生活が大好き',
    website = 'https://zhangsan.dev'
WHERE id = 1 AND is_deleted = 0;

-- ユーザーの論理削除
UPDATE users SET is_deleted = 1 WHERE id = 1;

-- ユーザー詳細のクエリ（削除済みを除外）
SELECT id, username, email, nickname, avatar_url, bio, website,
       article_count, follower_count, created_at
FROM users
WHERE id = 1 AND is_deleted = 0;

-- トップレベルカテゴリの作成
INSERT INTO categories (name, slug, description, sort_order)
VALUES ('テクノロジー', 'tech', 'テクノロジー関連の記事', 1),
       ('ライフ', 'life', '生活エッセイ', 2),
       ('読書', 'reading', '読書ノート', 3);

-- サブカテゴリの作成
INSERT INTO categories (name, slug, description, parent_id, sort_order)
VALUES ('フロントエンド', 'frontend', 'フロントエンド開発', 1, 1),
       ('バックエンド', 'backend', 'バックエンド開発', 1, 2),
       ('データベース', 'database', 'データベース技術', 1, 3),
       ('JavaScript', 'javascript', 'JavaScript言語', 4, 1),
       ('Python', 'python', 'Python言語', 5, 1);

-- カテゴリツリーのクエリ
SELECT
    c1.id, c1.name AS level1,
    c2.name AS level2,
    c3.name AS level3
FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
LEFT JOIN categories c3 ON c3.parent_id = c2.id
WHERE c1.parent_id IS NULL
ORDER BY c1.sort_order, c2.sort_order, c3.sort_order;

-- タグの作成
INSERT INTO tags (name, slug) VALUES
    ('MySQL', 'mysql'),
    ('JavaScript', 'javascript'),
    ('Python', 'python'),
    ('Docker', 'docker'),
    ('Redis', 'redis'),
    ('Vue.js', 'vuejs'),
    ('React', 'react'),
    ('アルゴリズム', 'algorithms');

-- タグのクエリ（記事数順にソート）
SELECT t.id, t.name, t.slug, t.article_count
FROM tags t
ORDER BY t.article_count DESC
LIMIT 20;

-- 記事の作成（下書き）
INSERT INTO articles (user_id, category_id, title, slug, summary, content, status)
VALUES (
    1,
    7,  -- JavaScriptカテゴリ
    'MySQLインデックス最適化 実践ガイド',
    'mysql-index-optimization-guide',
    'この記事ではMySQLインデックス最適化のコア戦略と実践テクニックを詳しく解説します',
    '# MySQLインデックス最適化\n\n## インデックスとは\n\nインデックスは...',
    0  -- 下書き
);

-- 記事の公開
UPDATE articles
SET status = 1,
    published_at = NOW()
WHERE id = 1 AND user_id = 1;

-- 記事にタグを付ける
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),  -- MySQL
    (1, 8);  -- アルゴリズム

-- 記事のタグを更新（削除してから挿入）
DELETE FROM article_tag WHERE article_id = 1;
INSERT INTO article_tag (article_id, tag_id) VALUES
    (1, 1),
    (1, 5);  -- MySQL + Redis

-- 記事の論理削除
UPDATE articles SET is_deleted = 1 WHERE id = 1 AND user_id = 1;

-- 非正規化フィールドの更新：ユーザーの記事数
UPDATE users SET article_count = (
    SELECT COUNT(*) FROM articles WHERE user_id = 1 AND is_deleted = 0
) WHERE id = 1;

-- 非正規化フィールドの更新：カテゴリの記事数
UPDATE categories SET article_count = (
    SELECT COUNT(*) FROM articles WHERE category_id = 7 AND status = 1 AND is_deleted = 0
) WHERE id = 7;

-- 非正規化フィールドの更新：タグの記事数
UPDATE tags SET article_count = (
    SELECT COUNT(*) FROM article_tag WHERE tag_id = 1
) WHERE id = 1;

-- コメントの投稿
INSERT INTO comments (article_id, user_id, content)
VALUES (1, 2, '素晴らしい記事ですね、とても勉強になりました！');

-- コメントへの返信
INSERT INTO comments (article_id, user_id, parent_id, reply_to_user_id, content)
VALUES (1, 3, 1, 2, '同感です。特にインデックスのセクションがとても分かりやすかったです');

-- 記事のコメント数を更新
UPDATE articles SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE article_id = 1 AND status = 1 AND is_deleted = 0
) WHERE id = 1;

-- コメントの論理削除
UPDATE comments SET is_deleted = 1 WHERE id = 1 AND user_id = 2;

-- ユーザーをフォロー
INSERT INTO user_follows (follower_id, following_id) VALUES (2, 1);
-- 非正規化フォロワー数を更新
UPDATE users SET follower_count = follower_count + 1 WHERE id = 1;

-- フォロー解除
DELETE FROM user_follows WHERE follower_id = 2 AND following_id = 1;
UPDATE users SET follower_count = follower_count - 1 WHERE id = 1;

-- フォロー中リストのクエリ
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.following_id = u.id
WHERE f.follower_id = 2 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

-- フォロワーリストのクエリ
SELECT u.id, u.username, u.nickname, u.avatar_url, u.bio
FROM user_follows f
JOIN users u ON f.follower_id = u.id
WHERE f.following_id = 1 AND u.is_deleted = 0
ORDER BY f.created_at DESC;

SELECT
    a.id,
    a.title,
    a.slug,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    -- 複合ホットスコア：閲覧数×1 + いいね数×5 + コメント数×3
    (a.view_count * 1 + a.like_count * 5 + a.comment_count * 3) AS hot_score,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tag at2 ON a.id = at2.article_id
LEFT JOIN tags t ON at2.tag_id = t.id
WHERE a.status = 1
    AND a.is_deleted = 0
    AND a.published_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY a.id
ORDER BY hot_score DESC
LIMIT 20;

SELECT
    u.id,
    u.username,
    u.nickname,
    u.article_count,
    u.follower_count,
    -- 総閲覧数
    IFNULL(SUM(a.view_count), 0) AS total_views,
    -- 総いいね数
    IFNULL(SUM(a.like_count), 0) AS total_likes,
    -- 総コメント数
    IFNULL(SUM(a.comment_count), 0) AS total_comments,
    -- 最新の公開記事日時
    MAX(a.published_at) AS last_published_at,
    -- 記事あたりの平均閲覧数
    IFNULL(ROUND(AVG(a.view_count), 0), 0) AS avg_views_per_article
FROM users u
LEFT JOIN articles a ON u.id = a.user_id AND a.status = 1 AND a.is_deleted = 0
WHERE u.id = 1 AND u.is_deleted = 0
GROUP BY u.id;

SELECT
    a.id,
    a.title,
    a.content,
    a.cover_image_url,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.updated_at,
    u.id AS author_id,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    u.bio AS author_bio,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.slug = 'mysql-index-optimization-guide'
    AND a.status = 1
    AND a.is_deleted = 0;

-- 記事のタグをクエリ
SELECT t.id, t.name, t.slug
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
WHERE at2.article_id = 1;

-- 閲覧数をインクリメント
UPDATE articles SET view_count = view_count + 1 WHERE id = 1;

-- トップレベルコメントのクエリ
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.id AS user_id,
    u.username,
    u.nickname,
    u.avatar_url
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.article_id = 1
    AND c.parent_id IS NULL
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at DESC
LIMIT 20;

-- 特定のコメントへの返信をクエリ
SELECT
    c.id,
    c.content,
    c.like_count,
    c.created_at,
    u.username,
    u.nickname,
    u.avatar_url,
    ru.username AS reply_to_username,
    ru.nickname AS reply_to_nickname
FROM comments c
JOIN users u ON c.user_id = u.id
LEFT JOIN users ru ON c.reply_to_user_id = ru.id
WHERE c.parent_id = 1
    AND c.status = 1
    AND c.is_deleted = 0
ORDER BY c.created_at ASC;

-- 特定のタグに紐づくすべての記事をクエリ
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.avatar_url AS author_avatar
FROM article_tag at2
JOIN articles a ON at2.article_id = a.id
JOIN users u ON a.user_id = u.id
WHERE at2.tag_id = 1
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- カテゴリごとの記事数と平均閲覧数
SELECT
    c.id,
    c.name,
    c.slug,
    c.article_count,
    IFNULL(COUNT(a.id), 0) AS actual_count,
    IFNULL(ROUND(AVG(a.view_count)), 0) AS avg_views,
    IFNULL(MAX(a.published_at), NULL) AS latest_article_at
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
    AND a.status = 1 AND a.is_deleted = 0
GROUP BY c.id
ORDER BY c.sort_order;

-- カテゴリとそのサブカテゴリすべての記事数をクエリ
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1  -- テクノロジーカテゴリ
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    ct.id,
    ct.name,
    COUNT(a.id) AS article_count
FROM category_tree ct
LEFT JOIN articles a ON a.category_id = ct.id AND a.status = 1 AND a.is_deleted = 0
GROUP BY ct.id, ct.name;

-- フォロー中のユーザーの最新記事をクエリ
SELECT
    a.id,
    a.title,
    a.slug,
    a.summary,
    a.view_count,
    a.like_count,
    a.published_at,
    u.username AS author,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar
FROM user_follows f
JOIN articles a ON f.following_id = a.user_id
JOIN users u ON a.user_id = u.id
WHERE f.follower_id = 2
    AND a.status = 1
    AND a.is_deleted = 0
ORDER BY a.published_at DESC
LIMIT 20;

-- 公開記事ビュー（よく使用されるため、クエリを簡素化）
CREATE VIEW v_published_articles AS
SELECT
    a.id,
    a.user_id,
    a.category_id,
    a.title,
    a.slug,
    a.summary,
    a.cover_image_url,
    a.status,
    a.is_pinned,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.published_at,
    a.created_at,
    u.username AS author_username,
    u.nickname AS author_nickname,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    c.slug AS category_slug
FROM articles a
JOIN users u ON a.user_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.status = 1 AND a.is_deleted = 0;

-- ビューを使用したクエリ
SELECT * FROM v_published_articles ORDER BY published_at DESC LIMIT 20;
SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC;

-- 記事タグサマリービュー
CREATE VIEW v_article_tags AS
SELECT
    at2.article_id,
    GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tag_names,
    GROUP_CONCAT(t.slug ORDER BY t.name SEPARATOR ', ') AS tag_slugs,
    COUNT(t.id) AS tag_count
FROM article_tag at2
JOIN tags t ON at2.tag_id = t.id
GROUP BY at2.article_id;

-- タグ付き記事のクエリ
SELECT
    vpa.*,
    vat.tag_names,
    vat.tag_count
FROM v_published_articles vpa
LEFT JOIN v_article_tags vat ON vpa.id = vat.article_id
ORDER BY vpa.published_at DESC
LIMIT 20;

-- 1. 記事リストクエリの最適化（カバリングインデックス）
-- WHERE status=1 AND is_deleted=0 ORDER BY published_at DESC 用
CREATE INDEX idx_articles_list ON articles(status, is_deleted, published_at DESC, id, title, slug, summary);

-- 2. 人気記事ランキングの最適化
CREATE INDEX idx_articles_hot ON articles(status, is_deleted, published_at, view_count, like_count, comment_count);

-- 3. カテゴリフィルタリングの最適化
CREATE INDEX idx_articles_category ON articles(category_id, status, is_deleted, published_at DESC);

-- 4. ユーザー記事リストの最適化
CREATE INDEX idx_articles_user ON articles(user_id, status, is_deleted, published_at DESC);

-- 5. コメントリストの最適化
CREATE INDEX idx_comments_article ON comments(article_id, status, is_deleted, parent_id, created_at);

-- 6. ユーザーフォロー関係の最適化
CREATE INDEX idx_follows_following ON user_follows(following_id, follower_id);

-- 7. インデックス使用状況の確認
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'blog_system'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- 8. スロークエリの分析
EXPLAIN SELECT * FROM v_published_articles WHERE category_id = 1 ORDER BY view_count DESC LIMIT 20;

SELECT v.title, v.view_count, u.name
FROM v_published_articles v
JOIN users u ON v.author_id = u.id
WHERE v.category_id = 1 ORDER BY v.view_count DESC LIMIT 20;