mysql - Finding SQL Query Bottleneck -

i learning sql. sharpen skills, i've found sql puzzle on coderloop.com. (great website programming puzzles, btw). working through puzzle:

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

i close solution. need optimize code. puzzle requires design course catalog database, along queries "add student course" or "get course details". have, think, working solution. have tested own data, , seems perform fine. coderloop uses thousands of queries stress test database schema , queries. unfortunately, solution fails due timeout after 4 minutes or so. have reviewed schema , queries , can't find bottleneck. ideas on how redesign database or queries speed things up?

notes:

• executes on mysql server
• the ?s parameters of test data passed coderloop bot. see bottom of 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),     primary 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 new professor

insert professors (name, surname, email, faculty, telephone, pid)  values (?, ?, ?, ?, ?, ?); 

add new student

insert students (name, surname, email, faculty, matriculation, sid)  values (?, ?, ?, ?, ?, ?); 

add new course

insert courses (course_name, subject, credits)  values (?, ?, ?); 

add existing professor existing course

(this crazy syntax match order of paramaters passed ?s)

update courses set pid = case when course_name = ? ? else pid end; 

add existing student existing course

insert enrollment (course_name, sid) values (?, ?); 

get list of students attending course

select name, surname students join enrollment on students.sid=enrollment.sid course_name=?; 

get professor owning course

select name, surname, professors.pid professors join courses on professors.pid=courses.pid course_name=?; 

get course details

select subject, credits courses course_name=?; 

get professor details

select name, surname, email, faculty professors pid=?; 

get student details

select name, surname, email, faculty, matriculation students sid=?; 

remove student course

delete enrollment sid=? , course_name=?; 

replace professor in course

update courses set pid=? course_name=?; 

add indexes on primary , foreign key fields. quite bit.

also, make sure have indexes on fields involved in , order clauses (in addition fk fields)

ultimately: going need narrow down specific queries ones performing can focus efforts there.