MySQL Query Performance and Codeigniter

Question

I have this MySQL query which I am loading in to my home controller and after running Codeigniter's $this->output->enable_profiler(TRUE); I get an execution time of 5.3044

The Query inside my model:

class Post extends CI_Model {

    function stream($uid, $updated, $limit) {
        $now = microtime(true);
        $sql = "
            SELECT 
                * 
            FROM 
                vPAS_Posts_Users_Temp 
            WHERE 
                post_user_id = ? 
                AND post_type !=4 
                AND post_updated > ?
                AND post_updated < ? 
            UNION 

            SELECT 
                u.* 
            FROM 
                vPAS_Posts_Users_Temp u 
            JOIN 
                PAS_Follow f 
                ON f.folw_followed_user_id = u.post_dynamic_pid 
                WHERE u.post_updated > ?
                AND post_updated < ? 
                AND (( f.folw_follower_user_id = ? AND f.folw_deleted = 0 ) 
                OR ( u.post_passed_on_by = f.folw_follower_user_id OR u.post_passed_on_by = ? AND u.post_user_id != ? AND u.post_type =4 )) 
            ORDER BY 
                post_posted_date DESC 
            LIMIT ?
        "; 

        $query = $this->db->query($sql, array($uid, $updated, $now, $updated, $now, $uid, $uid, $uid, $limit));

        return $query->result();
    }

}

Is there anything I can do here to improve the execution time and therefore increase my page load?

Edit

Explain Results MySQL Explain Results

MySQL Workbench Visual Explain

Have you indices set up on these two fields: f.folw_followed_user_id and u.post_dynamic_pid? — , Feb 09 '14 at 10:17
@jeff I have on the `f.folw_followed_user_id` but the other field is created dynamically within the query, so I am unsure as to how to do this — Justin Erswell, Feb 09 '14 at 10:24
use mysql workbench for check the performance of the query execution — saurabh kamble, Feb 09 '14 at 13:12
Have a look at the docs on explain : http://dev.mysql.com/doc/refman/5.6/en/explain-output.html this might help you optimise your tables. — , Feb 09 '14 at 13:19
@saurabh2836 I am struggling to see what I need to do here can you point me in the right direction? — Justin Erswell, Feb 09 '14 at 14:21
Your union is useless, You're not doing anything with it (unless I am missing something here). It would have been better if you provided the table structure & explained what you're trying to do. — ahmad, Feb 15 '14 at 19:56
Do you need ALL fields returned ? If not, avoid using `SELECT * FROM` but rather specify the columns you need. Additionally, am I right to assume vPAS_Posts_Users_Temp is a view ? If so, could you give the definition of said view? — deroby, Feb 16 '14 at 20:58

score 3 · Accepted Answer · edited May 23 '17 at 11:43

Maybe you won't believe it, but DON'T retrieve SELECT * in your SQL. Just write the fields you want to retrieve and I think it'll speed up a lot.

I've seen increases in speed of more than 20 times when executing a query (from 0.4secs to 0.02 secs) just changing * for required fields.

Other thing: If you have an auto_increment id on INSERT in your tables, DON'T use post_posted_date as ORDER field. Ordering by DATETIME fields is slow, and if you may use an INT id (which hopefully you will have as an index) you will achieve the same result quicker.

UPDATE

As required in the question, technical reasons:

For not using SELECT *: Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc. This is for SQL, but for MySQL (not as complete as question before) mySQL Query - Selecting Fields
For Ordering by Datetime: SQL, SQL Server 2008: Ordering by datetime is too slow, and again, related to MySQL: MySQL performance optimization: order by datetime field

Bonus: Learning how to set the indexes: http://ronaldbradford.com/blog/tag/covering-index/

score 1 · Answer 2 · answered Feb 14 '14 at 11:21

I would add indexes on post_user_id, post_updated and folw_follower_user_id.

In this case it may also be better to not use union and separate the query into two separate ones and then use PHP to combine to two result sets.

If you switched to using active record you could also look into caching, to get better performance

score 0 · Answer 3 · answered Feb 13 '14 at 21:37

The rows column shows an estimate for how many rows needs to be examined, which as I understand, means that in your case, it has to scan 72 * 1 * 2627 * 1 * 2 rows, which is quite a lot.

Now, the trick is to bring down this number, and one way is to add indexes. In your case, I would suggest adding an index which contains: post_user_id, post_type, post_updated, post_updated.
This should bring down the first result set, of 72 rows.

Now for the UNION, try using UNION ALL instead, as it is claimed to be faster.
If that doesn't fix the problem, I would suggest rewriting the query to not use a UNION call at all.

72 * 1 * 2627 * 1 * 2 = 378288 – Ronni Skansing Feb 19 '14 at 17:35 — Ronni Skansing, Feb 19 '14 at 17:35

score 0 · Answer 4 · answered Feb 14 '14 at 18:47

Try the query with left join as you are trying to union on same table

"SELECT 
u.* 
        FROM 
            vPAS_Posts_Users_Temp u
            LEFT JOIN PAS_Follow f ON f.folw_followed_user_id = u.post_dynamic_pid 
        WHERE (
        u.post_user_id = ? 
                AND u.post_type !=4 
                AND u.post_updated > ?
                AND u.post_updated < ?
    ) 
    OR 
     (
    u.post_updated > ?
        AND post_updated < ? 
        AND (( f.folw_follower_user_id = ? AND f.folw_deleted = 0 ) 
                OR ( u.post_passed_on_by = f.folw_follower_user_id OR u.post_passed_on_by = ? AND u.post_user_id != ? AND u.post_type =4 )) 
    )
        ORDER BY 
            u.post_posted_date DESC 
            LIMIT ?"

I did not consider all your condition, i just showed how you can do this with left join — Minhaz, Feb 16 '14 at 11:52

score 0 · Answer 5 · answered Feb 16 '14 at 10:36

I think you can remove the UNION from the query and make use of left join instead and avoid the unnecessary conditions:

SELECT U.* 
FROM vPAS_Posts_Users_Temp AS U 
LEFT JOIN PAS_Follow AS F ON F.folw_followed_user_id = U.post_dynamic_pid 
WHERE U.post_updated > ?
AND U.post_updated < ?
AND ( 
      ( 
        F.folw_follower_user_id = ? AND F.folw_deleted = 0 
      ) 
      OR 
      ( 
        U.post_passed_on_by = F.folw_follower_user_id OR U.post_passed_on_by = ? 
      )
    ) 
ORDER BY 
U.post_posted_date DESC 
LIMIT ?

Also identify and set proper indexes in your tables.

MySQL Query Performance and Codeigniter

5 Answers5

UPDATE