[El Forum]

[eluser]Unknown[/eluser]
I have a Project with Codeigniter. Database has two main table (categories, news)
- categories (20 records).
- news (200000 records and continue update).

I get all categories and 3 news of that category.
But when i run my website, It load complete in 5 - 15 seconds.

Please help me.
Sorry for my english

[El Forum]

[eluser]CroNiX[/eluser]
Make sure you have everything properly indexed. Any fields that you use a WHERE or a JOIN on should be indexed. Of course, it could also be the way you are doing your queries. There are many ways to optimize things, but you haven't showed us your db schema or queries or any code.

[El Forum]

[eluser]Unknown[/eluser]
My Function get navigation

Code:

public function get_navigation_header(){
    $result = array();
    $result = $this->array_category();
    foreach($result as $key=>$val){
        $result[$key]['submenu'] = $this->array_category($result[$key]['id']);
        $result[$key]['news'] = $this->hotnew_category($result[$key]['id']);
    }
    return $result;
}

Get news in here

Code:

$result[$key]['news'] = $this->hotnew_category($result[$key]['id']);

This is function get news

Code:

public function hotnew_category($id){
    $this->db->select('id,name,image,created,content,summary');
    $this->db->limit(3);
    $this->db->where('cat',$id);
    $this->db->order_by('created','DESC');
    $data = $this->db->get(TABN);
    $news = $data->result_array();
    foreach($news as $key=>$val){
        $news[$key]['link'] = $this->linkNew($news[$key]['id']);
        $news[$key]['image'] = $this->tungphut->get_image_instead($news[$key]['image'],$news[$key]['content']);
    }
    return $news;
}

This is function get link for a new

Code:

public function linkNew($id){
    $this->db->select('id,cat,rewrite');
    $this->db->where('id',$id);
    $data = $this->db->get(TABN);
    $new = $data->row_array();
    $url = $new['rewrite'].'.html';
    $this->db->where('id',$new['cat']);
    $data = $this->db->get(TABCAT);
    $category = $data->row_array();
    $url = $this->hasParent($category['parent'],$category['rewrite']).$url;
    $url = base_url($url);
    return $url;
}
public function hasParent($parent,$rewrite){
    $url = '';
    if($parent == 0){
        $url = $rewrite.'/';
    }
    else {
        $this->db->where('id',$parent);
        $data = $this->db->get(TABCAT);
        $category = $data->row_array();
        $url = $this->hasParent($category['parent'],$category['rewrite']).$rewrite.'/';
    }
    return $url;
}

[El Forum]

[eluser]jonez[/eluser]
Instead of doing 20 queries individually do them all as one query (group by) then delegate the response array. As CroNiX said indices will help a lot and foreign key's should be on all your join columns.

Your query should return something like this (16/12 are ex. category ID's);

Code:

1 - 16 - News - Post 1
2 - 16 - News - Post 2
3 - 16 - News - Post 3
4 - 12 - Sports - Post 1
5 - 12 - Sports - Post 2
6 - 12 - Sports - Post 3
...

Loop through it and push records into groups;

Code:

$records = $this->db->query( $sql, $params )->result_array( );
$results = array( );

foreach ( $records as $record ) {
  if ( !array_key_exists( $record[ 'cat_id' ], $results ) ) {
    $results[ $record[ 'cat_id' ] ] = array( );
  }

  $results[ $record[ 'cat_id' ][ ] = $record;
}