+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: active record query order (/showthread.php?tid=8158)
active record query order - El Forum - 05-07-2008
[eluser]skattabrain[/eluser]
I have a question, i have a very large database ... the inventory table has 500,000,000+ records. So query efficiency is important. I can shave down execution time by being clever about the order of my arguments.
for example, if i need to do something like this ...
select partnumber, qty, price, clientname from inv, clients where inv.Type = 'orange' and inv.Style = 'cool' and clients.clientid = inv.clientid
it's best if the the first "where" argument is going to remove the most possibilities from my results ... so if i index 'Type' and I know there are many less 'orange' Types versus Style = cool ... which could be millions, i should first say, give me type = orange.
so aside from indexing, the where argument with the most disqualification should be first, etc ...
but when i build my query using active record, it doesn't force what i want to be first. how do you control the order of your where & like arguments?
active record query order - El Forum - 05-07-2008
[eluser]skattabrain[/eluser]
ok, it is in order if you are using only where or only like, but if you mix, where is forced first. i guess this makes sense?
i guess i don't undestand if joins merge the tables together first before the where part of the query.
here is my actual query ...
Code:
$this->db->from('parts');
$this->db->like('parts.PartNumber', $pn, 'after');
if($region_filter != 'all')
{
$this->db->where('clients.region', $region_filter);
}
$this->db->join('inventory', 'inventory.partID = parts.partID');
$this->db->join('clients', 'clients.clientID = inventory.clientID');
$this->db->order_by('inventory.pnDisp','ASC');
$this->db->order_by('inventory.type','DESC');
$this->db->order_by('clients.scAvg','DESC');