Sql高效快速搜索

Question

I am making a large website using php and sql that will contain many users and i need to make my database as fast as possible. This is the code that get posts value from database table called upload and store them in an array

//connect to database
$a = 'localhost';
$b = 'root';
$c = '';
$d = 'petsnote';
$connect = mysqli_connect($a, $b, $c, $d);

//get posts images variables
$images = array();
$cat = array();
$id = array();

//get posts info
if($connect){
    $query = "SELECT * FROM `upload` WHERE `Active`='1'";
    if($query_run = mysqli_query($connect, $query)){
        while($get = mysqli_fetch_assoc($query_run)){
            array_push($images, $get['Image_Name']);
            array_push($cat, $get['PostCategory']);
            array_push($id, $get['Id']);
        }

        $loop = count($images);
    }
}

I get all the post information from the database and store them in an array and use that array to view only some values.. Is this an efficient, professional and fast way to do things?

Answer 1

Your query needs only to ask for things it will use:

SELECT Id, Image_Name, PostCategory FROM upload WHERE Active='1'

But this will not meaningfully speed up anything.

What's more important is the database structure. From MySQL run SHOW CREATE TABLE upload and ensure that every column you will search on (e.g. Active in this case) is indexed.

Though again, unless you're dealing with tens of thousands of rows, you're not going to see much of a difference.