MySQL query optimization in codeigniter

Question

I have function in CodeIgniter to retrieve latest posts from 2 tables:

public function get_latest_comments($amount)
{
  $query = $this->db->query('
    SELECT *, FROM_UNIXTIME(date) AS timestamp
    FROM comments
    ORDER BY timestamp DESC
    LIMIT 5
  ');

  if ($query->num_rows() > 0) {
    $result = $query->result_array();

    for ($i = 0; $i < sizeof( $result ); $i++) {
      $result[$i]['author_info'] = $this->comments_model->get_comment_author( $result[$i]['id'] );
      $result[$i]['date'] = mdate( "%M %m, %Y", $result[$i]['date'] );

      if ($result[$i]['section'] === 'blog') $loc = 'blog_posts';
      if ($result[$i]['section'] === 'core') $loc = 'public_posts';

      $this->db->select( 'title, slug' );
      $query = $this->db->get_where( $loc, array('id' => $result[$i]['location']) );

      $result[$i]['post_title'] = $query->row( 'title' );
      $result[$i]['url'] = base_url() . $result[$i]['section'] . '/view/' . $query->row( 'slug' ) . '/';
    }
    return $result;
  }
  return false;
}

The problem is that it runs too slow. My page sometimes loads 7-8 seconds. I suspect this query running 2 times + similar query gathering latest comments slows down my page.

I have a bad feeling about queries inside the loop. How can I avoid that?

The structure of my table is:

users (id, username, mail ...
user_info ( user_id, name, surname
public_posts ( id, slug, text, author(user id) ...
blog_posts ( id, slug, text ...
comments ( id, text, author, location(post_id_, section(post_table) ...

How about getting the comment other by joining it in the query instead of querying for each row? — eric.itzhak
– eric.itzhak, Commented Apr 3, 2013 at 9:14
I'm not sql guru, i would be grateful if you showed me an example — Emanuel Grimoqua
– Emanuel Grimoqua, Commented Apr 3, 2013 at 9:29
The query above really shouldn't be that slow even with the embedded query. The most common reason for slow queries is no indexes defined beyond a primary. For example in the above tables you'd want to set an index on user_id anywhere it appears and post_id as well. Just make sure you set them as indexes and not primary (unless it is for the right table) or unique. I think you'll find doing that makes a huge difference. You can do this on the structure page for each table in phpmyadmin — Rick Calder
– Rick Calder, Commented Apr 3, 2013 at 10:12
What does the profiler shows? Try adding a $this->output->enable_profiler(true); into your controller and check what queries ran and with what time. Adding explain before the individual queries should tell if you are doing a full table scans or not. — complex857
– complex857, Commented Apr 3, 2013 at 11:07

Vinod · Accepted Answer · 2017-11-12 10:53:55Z

3

Check by expalining your Query , Go to the mysql command line and type

EXPLAIN SELECT *, FROM_UNIXTIME(date) AS timestamp FROM comments ORDER BY timestamp DESC LIMIT 5

Explain will tell you everything about the query, On its bases you can decide for the indexing also. Make a practice to expalin every select query before using it in the code.

Plus you can also do profiling when ever you think your code is taking time. In codeigniter Profiler class is available, please go through the below link.

https://www.codeigniter.com/userguide3/general/profiling.html

edited Nov 12, 2017 at 10:53

Vinod

1963 silver badges11 bronze badges

answered Apr 3, 2013 at 11:17

Nishant

3,7141 gold badge22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query optimization in codeigniter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related