CODEWITHSUNDEEP
phpmysqloopquery-optimization
JonnoTheBonno
JonnoTheBonno

Reputation: 149

PHP Optimisation is this correct?

I have been creating a php application that makes quite a few queries to the database i'd say roughly around 30 or so each page load. This is needed due to the nature of the application. I am using OOP php techniques and optimising my queries as much as I can. Should I be using some sort of caching system? or would you say 30 is fine? Here is a typical query.

Ok so my __construct looks like this:

public function __construct($host = 'localhost', $user = 'root', $pass = 'root', $name = 'advert') 
    {   
        $this->_conn = new mysqli($host, $user, $pass, $name)
            or trigger_error('Unable to connect to the server, please check your credentials.', E_USER_ERROR);
    }

And one method like so.

$sql = "SELECT `advert_id`,
                        `ad_title`,
                        `ad_image` FROM adverts WHERE UNIX_TIMESTAMP() < `ad_expires` AND `ad_show` = 0 AND `ad_enabled` = 1 ORDER BY `ad_id` DESC LIMIT 1";

        $stmt = $this->_conn->prepare($sql);
        if ($stmt) {
            $stmt->execute();
            $stmt->bind_result($ad_id, $ad_title, $ad_image);
            $rows = array();
            while ($row = $stmt->fetch()) {
                $item = array(
                    'ad_id' => $ad_id,
                    'ad_title' => $ad_title,
                    'ad_image' => $ad_image
                );
                $rows[] = $item;
            }

The app is kinda like this throughout.

Thanks any feedback will be much appreciated.

**EDIT Sorry i meant to say 30 queries not 30 connections

Upvotes: 0

Views: 98

Answers (2)

evasilchenko
evasilchenko

Reputation: 1870

Just make one connection and re-use it. 30 connections is a lot considering that you might have multiple users.

Edit: initial question said connections. 30 queries is fine unless this is data that doesn't change very often. In this case you can first do a query to see if you need to pull data or if the cached data is fine to serve to the user.

Upvotes: 0

user680786
user680786

Reputation:

You should use caching when it will useful. If time of page generation without caching of queries is 3 seconds, and with caching - 0.03, then you should use caching, obviously. If caching not gives any noticeable boost - don't spend resources.

Upvotes: 1

Related Questions