Query Optimizationg

DylanButler · 03-08-2008, 11:30 PM

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:

Code:

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:

Code:

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)

HTML Code:

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:

Code:

--
-- 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=MyISAM  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=latin1;

How I'm currently doing it:

PHP Code:

  <?

$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** · 03-09-2008, 09:22 AM

At first glance;

PHP Code:

  <?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 · 03-09-2008, 02:20 PM

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.

Code:

 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 · 03-09-2008, 08:37 PM

Try a right outer join ?.?

DylanButler · 03-10-2008, 08:38 PM

FIGURED IT OUT!

Code:

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

03-08-2008, 11:30 PM	#1 (permalink)
DylanButler NamePros Regular Join Date: Jan 2006 Location: San Diego, CA Posts: 704 0.00 NP$ (Donate)	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: Code: 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: Code: 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) HTML Code: 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: Code: -- -- 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=MyISAM 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=latin1; How I'm currently doing it: PHP Code: <? $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. __________________ :great: -Dylan Butler EXAMP - San Diego Web Design Last edited by DylanButler; 03-08-2008 at 11:47 PM.

03-09-2008, 09:22 AM	#2 (permalink)
Eric NPQ's PA, Slave, and On Call Coder Technical Services Join Date: Mar 2005 Posts: 4,545 0.71 NP$ (Donate)	At first glance; PHP Code: <?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> "; } ?> __________________

03-09-2008, 02:20 PM	#3 (permalink)
DylanButler NamePros Regular Join Date: Jan 2006 Location: San Diego, CA Posts: 704 0.00 NP$ (Donate)	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. Code: 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 __________________ :great: -Dylan Butler EXAMP - San Diego Web Design Last edited by DylanButler; 03-09-2008 at 02:46 PM.

03-09-2008, 08:37 PM	#4 (permalink)
monaco NamePros Regular Join Date: Jul 2005 Location: Tucson, AZ Posts: 695 314.80 NP$ (Donate)	Try a right outer join ?.? __________________ My Website \| My Blog

03-10-2008, 08:38 PM	#5 (permalink)
DylanButler NamePros Regular Join Date: Jan 2006 Location: San Diego, CA Posts: 704 0.00 NP$ (Donate)	FIGURED IT OUT! Code: 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 __________________ :great: -Dylan Butler EXAMP - San Diego Web Design Last edited by DylanButler; 03-10-2008 at 08:56 PM.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Technical Services