There has to be a better way? - DB Queries

[eluser]Devon Lambert[/eluser]
I have an annoying DB query problem that is hurting the performance on my site and on the pages that make use of these functions/controllers. Does anyone know of a better way for me to produce the queries found in the model below? i.e. call it all in 1 function? produce a more efficient query? Use some CI magic?

For the time being, while I am working/debugging I have enabled the CI cache for this controller but I don't particularly like that as a production solution as I want the effect provided by presenting my users with random query calls.

Each time these this page is loaded it takes about 4-5 seconds on my xampp server. That could be a nightmare on a production server with actual traffic.

Here's the code:

MODEL:

Code:

function get_special() {

    $query = "SELECT stuff  FROM mytable WHERE flags = 'special'";    

    $result = $this->db->query($query);

    return $result->result_array();    

}



function get_top() {

    $query = "SELECT similar stuff  FROM mytable ORDER BY rating DESC LIMIT 0,2";

    $result = $this->db->query($query);

    return $result->result_array();    

}



function get_lastaccessed () {

    $query = "SELECT slightly different stuff FROM myothertable, mytable ORDER BY stuff.time DESC LIMIT 0, 2";

    $result = $this->db->query($query);

    return $result->result_array();

}

CONTROLLER:

Code:

// Create the special_items array        

$special_items = array();    

$special_items['special'] = $this->mymdl->get_special();

$special_items['top'] = $this->mymdl->get_top();

$special_items['lastaccessed'] = $this->mymdl->get_lastaccessed();

    

$this->load->view('myview', $special_items);

VIEW:

Code:

<div>

    <div>

        <h2>HEADING</h2>

        &lt;?php foreach($top as $top2): ?&gt;

        &lt;?php //My Foreach variables and other html go in here ?&gt;
        &lt;?php endforeach; ?&gt;

    </div>

    

    <div>

        <h2>HEADING</h2>

        &lt;?php foreach($special as $special2): ?&gt;

        &lt;?php //My Foreach variables and other html go in here ?&gt;
        &lt;?php endforeach; ?&gt;

    </div>    

    <div>

        <h2>HEADING</h2>

        &lt;?php foreach($lastaccessed as $lastaccessed2): ?&gt;

        &lt;?php //My Foreach variables and other html go in here ?&gt;
        &lt;?php endforeach; ?&gt;

    </div>    
<div class="clear"></div>