MySQL – Using GROUP_CONCAT for Better Query Performance

Well, it seems like all your data is meaningful, since you don't have a lot of it, and it all seems to have a role as a key or useful attribute.

If you have PK (by definition unique) on isbn in b_c, then this restricts a book to one class. Is that true? At that point you could argue the design that the class_id should simply then be an attribute of the book table and you don't even need the b_c table.

Since you already have a PK on b_c, I don't see the need for a surrogate key. Even if you were to expand to compound primary key on isbn, class_id to be able to allow multiple classes for a book, I don't really see a need for an additional surrogate unique key. In any case, it would only be an alternative unique key, I probably wouldn't make it the primary key and probably wouldn't use it in joins (joining to link tables is not terribly common as a foreign key, since they are usually identified by their parent or child as being part of a collection based on that relationship)

This is what I would do:

DROP TABLE IF EXISTS `books`;

CREATE TABLE `books` (
  `book_id` INT(10) NOT NULL AUTO_INCREMENT,
  `isbn` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`book_id`)
) COMMENT 'Books used at this school';

/* 
  Also consider a unique constraint here on isbn
  Also consider whether to allow NULL isbn when it isn't yet known
*/

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `class_id` INT(10) NOT NULL AUTO_INCREMENT,
  `teacher_id` SMALLINT(5) NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';

DROP TABLE IF EXISTS `b_c`;

CREATE TABLE `b_c` (
  `book_id` INT(10) NOT NULL,
  `class_id` INT(10) NOT NULL,
  PRIMARY KEY (`book_id`, `class_id`) -- note that book is no longer unique by itself
) COMMENT 'Books to classes';

ALTER TABLE `b_c` ADD FOREIGN KEY (book_id) REFERENCES `books` (`book_id`) ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`) ON UPDATE CASCADE;

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.