best way to show a mysql join

[G0ng]

I have the following tables:

students
id, name,....

lessons
id,description,....

studentslessons
id,studentid,lessonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studentslessons where students.name="john" AND
students.id=studentslessons.studentid
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

thanks

[Erwin Moller]

G0ng wrote:

I have the following tables:

students
id, name,....

lessons
id,description,....

studentslessons
id,studentid,lessonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studentslessons where students.name="john" AND
students.id=studentslessons.studentid
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

thanks

Hi

I fail to see how this is related to PHP. :P

But you can bet the second one is faster because it is only 1 query.

Many databases will first make an executionplan, then execute it.

The quality of the executionplan depends on the database, and possibly
'hints' it received.

In general: Less queries are faster then many queries, and most databases
make good executionplans.

Hope that helps.

Regards,
Erwin Moller

[Justin Koivisto]

G0ng wrote:

I have the following tables:

students
id, name,....

lessons
id,description,....

studentslessons
id,studentid,lessonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studentslessons where students.name="john" AND
students.id=studentslessons.studentid
IME, I find queries that actually use JOIN to be quite a bit quicker. In
your case, you should try something similar to the following:

SELECT students.name, lessons.description, studentslessons.grade
FROM students
INNER JOIN lessons
INNER JOIN studentslessons ON studentslessons.studentid = students.id
ON lessons.id = studentslessons.lessonid
WHERE students.name = 'John'
Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

With php, the fewer queries you issue, the more efficient the code will
likely be. That way all the work is being done on the mysql server
rather than the web server. The only thing you have to do then is
optimize the queries to be faster.

--
Justin Koivisto, ZCE - ju****@koivi.com
http://koivi.com