I have two tables, one is Student, which has studentid, personid,
grade, fees as its columns.
The other one is Person, which has personid, firstname, lastname,
address as its columns.
When given a studentid, I need to get the student' info, such as
lastname from the db tables.
I can do it with two queries as the following:
1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got
above";
Or I can do it with one query:
3. mysql> select p.lastname from Student s, Person p where
s.studentid=1 AND s.personid=p.personid
Both works. However, I can't tell which one is actually faster, since
MySQL shows me "1 row in set (0.00 sec)" for all these three queries.
Both studentid and personid are primary keys, so they are
automatically indexed.
Will both indexes be used in query number 3? I saw people said that
MySQL could use in a query only one index...Does it mean only one
index will be used in query number 3? I am wondering which one will
give better performance.
Thank you in advance.