Welcome Guest, Not a member yet? Register   Sign In
There has to be a better way? - DB Queries
#1

[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>
#2

[eluser]oddman[/eluser]
Firstly, I'd look at the database schema and see if it can be improved, if it can't, move onto step 2.

2. Indexes. Whenever you use a field in a where clause, it should be indexed. If not, you're asking for trouble.
#3

[eluser]Unknown[/eluser]
Check all queries with explain (http://dev.mysql.com/doc/refman/5.0/en/explain.html).
#4

[eluser]gtech[/eluser]
also set db_debug to FALSE in config/database.php you will be surprised how much things get speeded up.
[edit] maybe my understanding was incorrect, it seemed to speed up my automated testing quite significantly but that was on an old version I will do some performance tests when I have time.[/edit]
#5

[eluser]Devon Lambert[/eluser]
Thanks all.

Trying all three implementations this afternoon. I'll post results.
#6

[eluser]Devon Lambert[/eluser]
Sorry I didn't get back sooner to report the results, but I'm still trying the "index" fix. Turning DB debug off did not speed up the query process, or should I say, it did not speed it up enough for me to notice.

I ran a few SQL queries using EXPLAIN and realized that one of the queries could make use of an index but it still seemed to run fairly slow (6 - 7 seconds) for the query to pull.

When I have some more free time I will examine the DB further and see if there is another column that can benefit from an index. Will also re-evaluate the DB schema. I suppose now is better than the future to weed out issue in the DB.

Thanks again for the help all.




Theme © iAndrew 2016 - Forum software by © MyBB