Issue with Multiple Joins

[eluser]bfillmer[/eluser]
I'm pretty sure whatever is going on is being caused by my unfamiliarity with MySQL joins, but I can't seem to find enough information online to see what I'm doing wrong. Here is the code I'm using:

Code:

this->db->select('*')->from('pr_stages');

// Section 1
$this->db->join('pr_stages_races','pr_stages_races.stage_id = pr_stages.id');
$this->db->join('pr_states_races','pr_states_races.race_id = pr_stages_races.race_id');
$this->db->where('pr_states_races.state_id',$state_id);

// Section 2
$this->db->join('pr_types_stages','pr_types_stages.stage_id = pr_stages.id');
$this->db->join('pr_types','pr_types.id = pr_types_stages.type_id');
$this->db->where('pr_types.type',$type);

I'm attempting to do several joins to limit the results returned, preferably to get very fast count queries. They are labeled section one and two as if I comment out one of them the query will work, leaving both uncommented simply ends up with a page that never loads. As far as I can tell the joins aren't working because I'm attempting to join on pr_stages.id twice, is there any way around this?

Really just hoping someone with more MySQL knowledge can give me a 'DOH' moment with an easy solution.

[eluser]überfuzz[/eluser]
I don't know about doh moment, but you could share a moment with phpmyAdmin. If you're not dead sure of what you're doing. Test run your querys there, when you got a query that fetches just the right rows/columns go ahead and change it to active records.

[eluser]bfillmer[/eluser]
Turns out that the SQL it's producing is working fine, but it's super slow. Here's an example of the SQL being output:

Code:

SELECT *
FROM (
`pr_stages`
)
JOIN  `pr_types_stages` ON  `pr_types_stages`.`stage_id` =  `pr_stages`.`id`
JOIN  `pr_types` ON  `pr_types`.`id` =  `pr_types_stages`.`type_id`
JOIN  `pr_stages_races` ON  `pr_stages_races`.`stage_id` =  `pr_stages`.`id`
JOIN  `pr_states_races` ON  `pr_states_races`.`race_id` =  `pr_stages_races`.`race_id`
WHERE  `pr_states_races`.`state_id` =  '12'
AND  `pr_types`.`type` =  'rides'
LIMIT 0 , 30

This returns some results from the database fine, but takes about 20 seconds to do so. On queries where nothing is returned it's taking up to 45 seconds to do so. Anyone have any idea as to why this query would run so slow?

[eluser]jedd[/eluser]

This returns some results from the database fine, but takes about 20 seconds to do so. On queries where nothing is returned it's taking up to 45 seconds to do so. Anyone have any idea as to why this query would run so slow?

I'd crank it up at the CLI and get MySQL to explain it. If you don't know about EXPLAIN .. now is a great time to learn.

I think (!) -- but am not sure -- that if you're trying to join the same table twice, you should assign it a different name on the second run through (using AS). Worth a try, in any case.

I also have a gut feel that if you need to do this, your schema might be a bit sub-optimal.

[eluser]Matthieu Fauveau[/eluser]
Like jedd said, try to run EXPLAIN in front of your query, that will help you narrow down the problem.

On another note, your query seems overcomplicated and doing a SELECT * on big data sets is never good for the mysql server.

Also, providing SQL dumps of the database involved would help some of us willing enough to help you build up a better query.

[eluser]bfillmer[/eluser]
Yeah, SELECT * was only used now while testing so I could be certain I'm getting what I want. I've got something a bit simpler working now, but it still is not optimal on this dataset, it's super slow. Here's the CI code:

Code:

$this->db->select('COUNT(pr_stages_races.id) as total_races')->from('pr_stages_races');
            
$this->db->join('pr_types_stages','pr_types_stages.stage_id = pr_stages_races.stage_id');
$this->db->join('pr_types','pr_types.id = pr_types_stages.type_id');
            
$this->db->join('pr_states_races','pr_states_races.race_id = pr_stages_races.race_id');
$this->db->join('pr_states','pr_states.id = pr_states_races.state_id');
            
$this->db->where('pr_states.id',$state_id);
$this->db->where('pr_types.id',$type_id);

The reason this is so convoluted is that each race can have many stages. Stages can have a type. Meanwhile races get assigned states. This latest version of the query seems a bit more streamlined, but it's still running way to slow.

[eluser]Matthieu Fauveau[/eluser]
Seems you could do it with only two joins :

Code:

$this->db->select('COUNT(pr_stages_races.id) as total_races')->from('pr_stages_races');
            
$this->db->join('pr_types_stages','pr_types_stages.stage_id = pr_stages_races.stage_id AND pr_types_stages.type_id = '.$type_id);
            
$this->db->join('pr_states_races','pr_states_races.race_id = pr_stages_races.race_id AND pr_states_races.state_id = '.$state_id);

[eluser]jedd[/eluser]
To paraphrase Statler and Waldorf .. Show us your schema!

[eluser]bfillmer[/eluser]
Relevant schema:

Code:

-- --------------------------------------------------------

--
-- Table structure for table `pr_stages_races`
--

CREATE TABLE IF NOT EXISTS `pr_stages_races` (
  `id` int(11) NOT NULL auto_increment,
  `race_id` int(11) NOT NULL default '0',
  `stage_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `pr_states_races`
--

CREATE TABLE IF NOT EXISTS `pr_states_races` (
  `id` int(11) NOT NULL auto_increment,
  `state_id` int(11) NOT NULL default '0',
  `race_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `pr_types_stages`
--

CREATE TABLE IF NOT EXISTS `pr_types_stages` (
  `id` int(11) NOT NULL auto_increment,
  `type_id` int(11) NOT NULL default '0',
  `stage_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

Using this code now to select from the database:

Code:

$this->db->select('COUNT(pr_stages_races.id) as total_races')->from('pr_stages_races');
$this->db->join('pr_types_stages','pr_types_stages.stage_id = pr_stages_races.stage_id AND pr_types_stages.type_id = '.$type_id);
$this->db->join('pr_states_races','pr_states_races.race_id = pr_stages_races.race_id AND pr_states_races.state_id = '.$state_id);

This is working great now, however it can still be slow. Here are two example urls:

Fairly quick: http://www.stormlabdev.com/pr/races/map_xml/rides
Slow: http://www.stormlabdev.com/pr/races/map_xml/mtb

Same query, only the type is changing. Each page loops through the 51 states and runs the query to get the count of total races (really stages) in the database that match a state/type condition.

[eluser]bfillmer[/eluser]
Did anyone else have any ideas on how I can streamline this query some more?