+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: SQL optimization via Active Record (/showthread.php?tid=5753)
SQL optimization via Active Record - El Forum - 02-02-2008
[eluser]taewoo[/eluser]
Hi everyone.
I am writing a standard user-to-user messaging application.
So every time someone sends or receives a message from another user, I'd have to query the USERS table to get their username, first name, last name, picture, etc.
To make it more efficient, I was thinking of doing something like (in pseudo-code):
Code:
select USER_NAME, FIRST_NAME, LAST_NAME, PICTURE from USERS where USERS.id IN ((select ID from senders) UNION (SELECT ID from recipients));Is there a way to do this w/Active Record or do I need to spell it out w/raw SQL? Particularly the "in" comparison operator... is there a documentation for this?
SQL optimization via Active Record - El Forum - 02-02-2008
[eluser]Derek Allard[/eluser]
There is a where_in() function in AR yes, but this particular query looks very (very) complex, and I think you're probably better off with just a
Code:
$this->db->query("select USER_NAME, FIRST_NAME, LAST_NAME, PICTURE from USERS where USERS.id IN ((select ID from senders) UNION (SELECT ID from recipients))");SQL optimization via Active Record - El Forum - 02-02-2008
[eluser]taewoo[/eluser]
Thanks Derek.
By the way, I saw your video tutorials. Pretty good stuff. Your video is probably the #1 reason why I switched from cakephp to CI
SQL optimization via Active Record - El Forum - 02-03-2008
[eluser]xwero[/eluser]
I see you are using a subquery for the in values. It isn't supported in mysql before version 5 and i heard the performance is slow. Maybe you should do some benchmarking running two queries versus one. I would love to see some results (a quick search did't get any results).