更新查询以反映规范化的数据库

Question

我试图使一篇文章网站，但我想正常化我目前的数据库。我有四个表，除了一个之外，每个表都标记一个类别。这四个表是:娱乐、生活方式、科学和文章。文章是娱乐、生活方式和科学的所有条目的组合。我想删除“娱乐”、“生活方式”和“科学”，只留下文章表，这样可以节省空间并提高性能。我面临的唯一问题是一个查询，它通过获取表的最后一个ID来生成随机文章，然后获得一个介于6和最大ID之间的随机数。

我的所有表都具有以下结构：id (唯一)、category (文章的类型，例如娱乐)、title (文章的标题)、image (文章的图像网址)、link (文章的网址)、Counter (本文的视图数量)和dateStamp (文章的发布日期)。这是我试图基于我的规范化数据库更新的查询。

<?php
  //SELECT the MAX id from the Entertainment table
    $MAX_ID = $db->query("SELECT MAX(id) FROM Entertainment");

  //Get Max id value
    $MAX_ID = $MAX_ID->fetch_array();
    $MAX_ID = $MAX_ID[0];

   //Create random number variable
    $RAND_NUM;

  //If the Max ID is less than 6, make $MAX_ID the $RAND_NUM
    if ($MAX_ID < 6) {
        $RAND_NUM = $MAX_ID;

    } 

  //Else get a random value between 6 and the Max ID
    else {
        $RAND_NUM = mt_rand(6, $MAX_ID);

    }

  //Grab 6 articles by descending from the random number, example: If $RAND_NUM is 7, get all entries from 7-2
    $resultSet = $db->query("SELECT * FROM Entertainment WHERE id <= $RAND_NUM ORDER BY id DESC LIMIT 6");

    if ($resultSet->num_rows != 0) {
      //Booleans to check where we are at in the print off
        $conditional  = true;
        $conditional2 = true;
        echo "<div class='row'>";
        while ($rows = $resultSet->fetch_assoc()) {
            $image = $rows["image"];
            $title = $rows["title"];
            $link  = $rows["link"];
            $count = number_format($rows["Counter"]);

           //Print off these articles on the left
            if ($conditional == true && $conditional2 == true) {
                echo "<div class='left'><a href='$link'><img src=$image><p>$title</p></a><p id='article-views'><span class='glyphicon glyphicon-fire'></span> $count Views</p></div>";

                $conditional2 = false;
            }

           //Print off these articles on the right
            else {
                echo "<div class='right'><a href='$link'><img src=$image><p>$title</p></a><p id='article-views'><span class='glyphicon glyphicon-fire'></span> $count Views</p></div><hr>";

                $conditional2 = true;
            }


        }

        echo "</div>";
    }


?>

我应该如何更改/更新此查询，以便从文章表中获得随机娱乐文章？我对任何可以提高查询性能的更改都持开放态度。

Answer 1

而不是选择一个随机的结束ID，只需随机化所有行并从它们中选择6。

SELECT *
FROM Articles
WHERE category = 'Entertainment'
ORDER BY RAND()
LIMIT 6

如果需要避免使用ORDER BY RAND()，因为它的性能不够好，请参见How can i optimize MySQL's ORDER BY RAND() function?