best way to show a mysql join

[G0ng]

I have the following tables:

students
id, name,....

lessons
id,description, ....

studentslessons
id,studentid,le ssonid,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,studen tslessons where students.name=" john" AND
students.id=stu dentslessons.st udentid


Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

thanks

[Erwin Moller]

Re: best way to show a mysql join

G0ng wrote:
[color=blue]
> I have the following tables:
>
> students
> id, name,....
>
> lessons
> id,description, ....
>
> studentslessons
> id,studentid,le ssonid,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,studen tslessons where students.name=" john" AND
> students.id=stu dentslessons.st udentid
>
>
> Which is more efficient when using php4 and mysql 4.1? I'm using a
> rather old computer Pentium 800MHz.
>
> thanks[/color]

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]

Re: best way to show a mysql join

G0ng wrote:[color=blue]
> I have the following tables:
>
> students
> id, name,....
>
> lessons
> id,description, ....
>
> studentslessons
> id,studentid,le ssonid,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,studen tslessons where students.name=" john" AND
> students.id=stu dentslessons.st udentid[/color]

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.descrip tion, studentslessons .grade
FROM students
INNER JOIN lessons
INNER JOIN studentslessons ON studentslessons .studentid = students.id
ON lessons.id = studentslessons .lessonid
WHERE students.name = 'John'
[color=blue]
> Which is more efficient when using php4 and mysql 4.1? I'm using a
> rather old computer Pentium 800MHz.[/color]

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 - justin@koivi.co m

http://koivi.com