Updating query to reflect normalized database

Question

I am trying to make an article website, but I want to normalize my current database. I have four tables each labeling a category except for one. The four tables are: Entertainment, Lifestyle, Science, and Articles. Articles is the combination of all the entries of Entertainment, Lifestyle, and Science. I want to delete Entertainment, Lifestyle, and Science and just leave the Articles table, thus saving space and increasing performance. The only problem I am facing though is with a query that generates random articles by getting the last ID of the table, and then getting a random number between 6 and the max ID.

All my tables have the following structure: id (unique), category (the type of article this is, i.e Entertainment), title (title of the article), image (image URL of the article), link (URL of the article), Counter (number of views this article has), and dateStamp (the date the article was published). Here's the query I am trying to update based on my normalized database.

<?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>";
    }


?>

How should I change/update this query to make it so that I get random Entertainment articles from the Articles table? I am open to any changes that improve the performance of the query as well.

FYI, your old method assumed that low-numbered article IDs were never deleted. — Barmar, Apr 01 '16 at 02:33

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

Instead of selecting a random ending ID, just randomize all the rows and select 6 from them.

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

If you need to avoid ORDER BY RAND() because it doesn't perform well enough, see How can i optimize MySQL's ORDER BY RAND() function?

edited May 23 '17 at 11:50

Community

1
1

answered Apr 01 '16 at 02:31

Barmar

741,623
53
500
612

I heard that ORDER BY RAND() is horrible with performance, that's why I've been avoiding it. – user2896120 Apr 01 '16 at 02:35
That's true. See the question I linked to. – Barmar Apr 01 '16 at 02:37
Is your table large enough that it's really causing a performance problem, or are you just assuming it will. Don't assume, measure it. – Barmar Apr 01 '16 at 02:37
Right now ORDER BY RAND() would work perfect because the table is small, but 3 months or so I know it will cause problems, that's why I want to have a fully functional query that'll optimize on performance in the long run – user2896120 Apr 01 '16 at 02:40

Updating query to reflect normalized database

1 Answers1