Faster MySQL Join for DISTINCT records

Question

After reviewing the related questions, none of them seem to address mine, so here goes:

I have two tables containing Course information. One table [course] holds, currently, nearly 25k records with fields such as CourseName, CourseCode, Term, Instructor, etc. The second table [courseCatalog] holds merely the CourseCode and CourseName.

My application allows users to pull up an instructor and approve the courses from a list that they are allowed to teach.

I built a search with auto-suggest (jQuery Ajax Object -> PHP file -> MySQL and back) to find courses. When they are returned the user can click the course to mark (Another Ajax call to PHP & MySQL) it as an approved course for the instructor.

The problem is, there are some courses in the larger table that are not in the smaller table, and vice versa (eliminating that problem is a much harder issue based on where the data actually comes from, i digress)

If I join the tables on any particular field, certain courses are left out. LEFT JOIN still seems to yield the same problem because I'm forced to JOIN ON a field.

If I simply call to both tables:

SELECT DISTINCT course.CourseCode, course.CourseName
FROM course, courseCatalog

The query takes FOREVER which renders the search function useless, as it takes to long to load the suggestions.

I'd like to get a DISTINCT list of courses, in order, whether they appear in the 'course' table or the 'courseCatalog' table...with great haste :)

Any suggestions? Have I overlooked something simple? Thanks all

Answer 1

The Query you say takes forever is doing a completely redundant cartesian join where you are generating a whole load of additional rows (Number of rows in course multiplied by Number of rows in courseCatalog) then removing them again with DISTINCT to leave you with the original contents of the course table. (Same result as SELECT DISTINCT CourseCode, CourseName FROM course)

To get a distinct list of CourseCodes and CourseNames from both tables you can use the UNION operator.

SELECT CourseCode, CourseName
FROM course
UNION /*Will ensure DISTINCTness*/
SELECT CourseCode, CourseName
FROM courseCatalog
ORDER BY CourseCode, CourseName