MySQL and Codeigniter query performance

Question

I have this MySQL query that I upload to my home controller, and after running Codeigniter $this->output->enable_profiler(TRUE);I get the execution time5.3044

The request inside my model is:

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 to improve run time and therefore increase page load?

Edit

Explain the results

MySQL Workbench Visual Explain

Answer 1

You may not believe it, but DO NOT fetch SELECT * in your SQL. Just write the fields you want to get, and I think it will speed up a lot.

20 ( 0,4 0,02 ), * .

: auto_increment INSERT , post_posted_date ORDER. DATETIME , INT- (, , ), .

UPDATE

, :

• SELECT *: /? SELECT * SELECT column1, colum2, column3 ... SQL, MySQL ( , ) mySQL Query -

• Ordering by Datetime: SQL, SQL Server 2008: MySQL: MySQL:

: : http://ronaldbradford.com/blog/tag/covering-index/

Answer 2

post_user_id, post_updated folw_follower_user_id.

union , PHP .

, caching,

Answer 3

, , , , 72 * 1 * 2627 * 1 * 2, .

, , . , : post_user_id, post_type, post_updated, post_updated.
72 .

UNION UNION ALL, , , .
, , UNION .

Answer 4

Try a query with left join when you try to join into the 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 ?"

Answer 5

I think you can remove UNION from the query and use the left join instead and avoid 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 the appropriate indexes in your tables.