Finding SQL Query Bottleneck

I am learning SQL. To sharpen my skills, I've found a SQL puzzle on Coderloop.com. (Great website for programming puzzles, btw). I am currently working through this puzzle:

http://www.coderloop.com/puzzles/university

I am very close to a solution. I just need to optimize my code. The puzzle requires you to design a course catalog database, along with queries like "add a student to a course" or "get course details". I have, what I think, is a working solution. I have tested it with my own data, and it seems to perform fine. But Coderloop uses thousands of queries to stress test my database schema and queries. Unfortunately, my solution fails due to a timeout after about 4 minutes or so. I have reviewed my schema and queries and I can't find the bottleneck. Any ideas on how to redesign my database or queries to speed things up?

Notes:

• Executes on a MySQL Server
• The ?s are parameters of test data passed by the Coderloop bot. See the very bottom of this page.

My Soultion

Setup Database

CREATE TABLE students (
    sid int NOT NULL,
    name varchar(255),
    surname varchar(255),
    email varchar(255),
    faculty varchar(255),
    matriculation int,
    PRIMARY KEY (sid)
    );

CREATE INDEX stu_index ON students (sid);   
CREATE TABLE professors (
    pid int NOT NULL,
    name varchar(255),
    surname varchar(255),
    email varchar(255),
    faculty varchar(255),
    telephone varchar(255),
    P开发者_如何学PythonRIMARY KEY (pid)
);
CREATE INDEX pro_index ON professors (pid); 
CREATE TABLE courses (
    course_name varchar(255) NOT NULL,
    pid int,
    credits int,
    subject varchar(255),
    PRIMARY KEY (course_name)
);
CREATE INDEX cou_index ON courses (course_name);    
CREATE TABLE enrollment (
    course_name varchar(255),
    sid int
);

Add a new professor

INSERT INTO professors (name, surname, email, faculty, telephone, pid)  VALUES (?, ?, ?, ?, ?, ?);

Add a new student

INSERT INTO students (name, surname, email, faculty, matriculation, sid)  VALUES (?, ?, ?, ?, ?, ?);

Add a new course

INSERT INTO courses (course_name, subject, credits)  VALUES (?, ?, ?);

Add an existing professor to an existing course

(This crazy syntax is to match the order of paramaters passed to the ?s)

Update courses Set pid = Case When course_name = ? Then ? Else pid End;

Add an existing student to an existing course

INSERT INTO enrollment (course_name, sid) VALUES (?, ?);

Get list of students attending a course

SELECT name, surname FROM students JOIN enrollment ON students.sid=enrollment.sid WHERE course_name=?;

Get professor owning a course

SELECT name, surname, professors.pid FROM professors JOIN courses ON professors.pid=courses.pid WHERE course_name=?;

Get course details

SELECT subject, credits FROM courses WHERE course_name=?;

Get professor details

SELECT name, surname, email, faculty FROM professors WHERE pid=?;

Get student details

SELECT name, surname, email, faculty, matriculation FROM students WHERE sid=?;

Remove a student from a course

DELETE FROM enrollment WHERE sid=? AND course_name=?;

Replace a professor in a course

UPDATE courses SET pid=? WHERE course_name=?;

Add indexes on the primary and foreign key fields. That will help quite a bit.

Also, make sure you have indexes on fields involved in WHERE and ORDER BY clauses (in addition to the FK fields)

Ultimately: You are probably going to need to narrow it down to which specific queries are the ones performing slowly so you can focus your efforts there.

Think you need

CREATE INDEX enr_coursename ON enrollment(course_name);
CREATE INDEX co_pid ON courses(pid);