MySQL Query Performance and Codeigniter
codeigniter, mysql, php, sql
Solution
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/
Problem
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 Workbench Visual Explain