Query Optimizationg

[DylanButler]

Query Optimization

I am GOING CRAZY over this query. I can't figure it out.

What I want:

User logs in, we need to grab all courses and the status of that user in relation to a particular class (if available).
Format: A list of school courses and each course may or may not have a status for the user associated to it.

Table expected:

classId    |     [status]
---------------------
13           |       taken
14           |
15           |       ip

(continued for a list of all classes (unique), notice status is optional)

These are some queries I was trying:

SELECT c.classId, sc.status
FROM class AS c
LEFT OUTER JOIN scheduleClasses AS sc, user AS u, schedule AS s ON sc.scheduleId = '21' && sc.scheduleId = s.scheduleId && u.userId = '11' && c.classId = sc.classId && ( c.qtr = '1' || c.qtr = '2' || c.qtr = '3' || c.qtr = '4' )
ORDER BY ordinal
LIMIT 0 , 30

(Returns too many rows)

SELECT c.classId, sc.status
FROM class AS c
INNER JOIN scheduleClasses AS sc, user AS u, schedule AS s ON sc.scheduleId = '21' && sc.scheduleId = s.scheduleId && u.userId = '11' && c.classId = sc.classId && ( c.qtr = '1' || c.qtr = '2' || c.qtr = '3' || c.qtr = '4' )
ORDER BY ordinal
LIMIT 0 , 30

(This does what I want, only it doesn't return enough rows.) It should return a list of ALL classes, and if that user has a status set for a particular class, then return that too.

Table setup:

--
-- Table structure for table `class`
--

CREATE TABLE IF NOT EXISTS `class` (
  `classId` int(3) NOT NULL auto_increment,
  `course` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `descrip` text NOT NULL,
  `prereqs` varchar(255) NOT NULL default '',
  `qtr` int(2) NOT NULL default '0',
  `ordinal` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`classId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;

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

--
-- Table structure for table `schedule`
--

CREATE TABLE IF NOT EXISTS `schedule` (
  `scheduleId` int(5) NOT NULL auto_increment,
  `userId` int(5) NOT NULL default '0',
  `hashId` int(10) NOT NULL default '0',
  PRIMARY KEY  (`scheduleId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=82 ;

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

--
-- Table structure for table `scheduleClasses`
--

CREATE TABLE IF NOT EXISTS `scheduleClasses` (
  `scheduleId` int(5) NOT NULL default '0',
  `classId` int(5) NOT NULL default '0',
  `status` varchar(5) NOT NULL default '',
  `grade` char(1) NOT NULL default '',
  `notes` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

How I'm currently doing it:

<?
$sql = mysql_query("select classId, title from class where qtr = '1' || qtr = '2' || qtr = '3' || qtr = '4' order by ordinal");
while ($row = mysql_fetch_array($sql)) {	
	$classid = $row['classId'];
	$innersql = mysql_query("select status from scheduleClasses where scheduleId = '$scheduleId' and classId = '$classid'");
	$innerrow = mysql_fetch_array($innersql);
	$status = $innerrow[0]; 
	echo "<div class=\"class $status\" id=\"class-".$row['classId']."\">
		<a href=\"#\" class=\"name\">".$row['title']."</a>
		<a href=\"#nogo\" class=\"open\"><span>Open</span></a>
		<div class=\"options\">
			<a href=\"#\" class=\"taken\">Taken</a>
			<a href=\"#\" class=\"ip\">In progress</a>
			<a href=\"#\" class=\"not\">Not taken</a>
		</div>
	</div>";
}
?>

As you can see, we are sending a query within each iteration of the loop, which I dont think should be necessary here.
Let me know if you need more info.

 

[Eric]

At first glance;

<?php

// How is $scheduleId determined?

$sql = mysql_query("
	SELECT c.classId, c.title, sc.status
	FROM class AS c
	LEFT JOIN scheduleClasses AS sc (ON c.classId = sc.classId)
	WHERE c.qtr IN(1,2,3,4)
		AND sc.scheduleId = '$scheduleId'
	ORDER BY c.ordinal
") or die(mysql_error());

while ($row = mysql_fetch_array($sql))
{
	echo "
	<div class=\"class $row[status]\" id=\"class-$row[classId]\">
        <a href=\"#\" class=\"name\">$row[title]</a>
        <a href=\"#nogo\" class=\"open\"><span>Open</span></a>
        <div class=\"options\">
            <a href=\"#\" class=\"taken\">Taken</a>
            <a href=\"#\" class=\"ip\">In progress</a>
            <a href=\"#\" class=\"not\">Not taken</a>
        </div>
    </div>
";
}

?>

 

[DylanButler]

scheduleId is set previously through a cookie.

Your version threw up errors because of the line with the parenthesis:

( ON c.classId = sc.classId )

So I changed it to this, which returns not enough listings. I should note the scheduleClasses table may or may not have an entry for each class, but it will never have two entries for the same class and schedule.

 SELECT c.classId, c.title, sc.status
FROM class AS c
LEFT JOIN scheduleClasses AS sc ON ( c.classId = sc.classId )
WHERE c.qtr
IN ( 1, 2, 3, 4 )
AND sc.scheduleId = '21'
ORDER BY c.ordinal
LIMIT 0 , 30

Edit: could this help be adapted for this situation?

http://blogs.x2line.com/al/articles/184.aspx

 

[monaco]

Try a right outer join ?.?

 

[DylanButler]

FIGURED IT OUT!

SELECT c.classId, c.title, sc.status
FROM class AS c
  LEFT JOIN scheduleClasses AS sc ON (c.classId = sc.classId AND sc.scheduleId = 21 )
    WHERE c.qtr IN(1,2,3,4)
        
ORDER BY c.ordinal

Thanks guys