Nested JOINS slowing down my query... How do I optimize this?

[eluser]stormbytes[/eluser]
Code speaks for itself. If I comment out even 1 or 2 of the joins, the query runs much faster.

What's a better way to do this?

Code:

public function get_targets($asset_id)
{
    // Table: target_age
    $fields = 't_age.id age_id, ';

    // Table: target_gender
    $fields.= 't_gender.id gen_id,' ;

    // Table: target_income_range
    $fields.= 't_income.id inc_id, ';

    // Table: target_language, target_language_map
    $fields.= 't_lang.id lang_id,';

    // Table: target_political
    $fields.= 't_poli.id poli_id, ';

    // Table: target_region
    $fields.= 't_reg.id reg_id, ';

    // Table: target_sector
    $fields.= 't_sec.id sec_id';

    // -- Joins : Metadata
    
    $this->db->join('target_age_map age_map', 'age_map.asset_id = ua.id');
    $this->db->join('target_age t_age', 't_age.id = age_map.age_group_id');

    $this->db->join('target_gender_map gender_map', 'gender_map.asset_id = ua.id');
    $this->db->join('target_gender t_gender', 't_gender.id = gender_map.gender_id');

    $this->db->join('target_income_range_map income_map', 'income_map.asset_id = ua.id');
    $this->db->join('target_income_range t_income', 't_income.id = income_map.income_range_id');

    $this->db->join('target_language_map lang_map', 'lang_map.asset_id = ua.id');
    $this->db->join('target_language t_lang', 't_lang.id = lang_map.language_id');

    $this->db->join('target_political_map poli_map', 'poli_map.asset_id = ua.id');
    $this->db->join('target_political t_poli', 't_poli.id = poli_map.political_id');

    $this->db->join('target_region_map reg_map', 'reg_map.asset_id = ua.id');
    $this->db->join('target_region t_reg', 't_reg.id = reg_map.region_id');
    
    $this->db->join('target_sector_map sec_map', 'sec_map.asset_id = ua.id');
    $this->db->join('target_sector t_sec', 't_sec.id = sec_map.sector_id');

    // -- Run Query ---
    
    $this->db->select($fields);
    
    $query = $this->db->get_where('user_assets ua', array('ua.id' => $asset_id))->row();
            
    return $query;

[eluser]jmadsen[/eluser]
Your problem has nothing to do with CI or Active Record. Really, you should be asking on an sql forum, but we'll do what we can :-)

We need to understand your table sizes are, what indexes you have, can you use views for query, what does your Query optimizer tell you, is this a candidate for a denormalized table, etc.

I'd also like to know what your "_map" tables are..are these JOIN tables, and are they really necessary? It's possible your schema is a little off. Just for example, can you explain this relationship:

Code:

$this->db->join('target_gender_map gender_map', 'gender_map.asset_id = ua.id');
    $this->db->join('target_gender t_gender', 't_gender.id = gender_map.gender_id');

If you want us to be able to help you with this, I would first read a bit on query optimization on different websites and see what general types of advice are given, so you can anticipate and supply us with the info we'll need to help you.

After that, happy to make suggestions.

[eluser]vitoco[/eluser]
can you post the resulting query ? it's easy to analize.

[eluser]skunkbad[/eluser]
I was wondering about the performance of joins this week, but after seeing how many you are using in a single query, I don't feel so bad. 14 joins is at least double of the most joins I have in any query. I'm not using Active Record though, so I can't tell you if that has anything to do with it. Maybe you can try to write your query out and not use active record. It would be worth doing, because it couldn't take more than 5 minutes, and you'd be able to see if there is a difference.

[eluser]Frank Wong[/eluser]
That query generated is very inefficient for the database to handle. It would be much better if you just use one query to get the ua.id then using that to run 7 other queries that does joins between only two tables.

I am assuming you have already checked to make sure you have proper indexes in place.