active record query order

[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?

[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');