CODEWITHSUNDEEP
phpmysqlperformanceoop
Bart van Heukelom
Bart van Heukelom

Reputation: 44124

Two queries vs one query, performance

In PHP+MySQL+PDO, would it be much slower to do

than to just

The latter is only one query, so obviously faster. The former makes for cleaner code though (because the item's ID is often also just known beforehand), so it would be better if the performance difference is small enough.

The code where I'm using this:

public function actionView() {

    // read http request
    ...

    // get item
    $itemModel = new Rust_Model_Item();
    try {
        $id = $itemModel->getItemIdByUrlTitle($urltitle);
        $item = $itemModel->getItem($id); // lots of data
    } catch (Rust_Model_Item_NotFoundException $e) {
        throw new FastMVC_Web_Error(404, 'Item not found');
    }

    ...

}

http://code.heukelom.net/filedetails.php?repname=rust&path=%2Ftrunk%2Flib%2Fclasses%2FRust%2FController%2FItem.php

public function getItem($id) {

    $item = $this->getItemBasics($id);

    $catModel = new Rust_Model_Category();
    $item['category'] = $catModel->getById($item['category_id']);

    $ratingModel = new Rust_Model_Rating();
    $item['rating'] = $ratingModel->getForItem($id);

    $pageModel = new Rust_Model_Page();
    $item['pages'] = $pageModel->getListForItem($id);

    $tagModel = new Rust_Model_Tag();
    $item['tags'] = $tagModel->getForItem($id);

    return $item;

}

http://code.heukelom.net/filedetails.php?repname=rust&path=%2Ftrunk%2Flib%2Fclasses%2FRust%2FModel%2FItem.php

Upvotes: 0

Views: 310

Answers (2)

northpole
northpole

Reputation: 10346

Why not create a single query that gets the item data by id, putting an index on the id.

Upvotes: 1

duckyflip
duckyflip

Reputation: 16509

You should design your query so that the fields that you use in your WHERE clause have proper keys and indexes set-up on them, and only use one query to select them.

Upvotes: 2

Related Questions