469,574 Members | 1,662 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,574 developers. It's quick & easy.

Two queries VS one query--an index usage question

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.
Jul 20 '05 #1
1 1659
Henry wrote:
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.


Well, how much faster than 0.00 seconds can it get? ;-)
I'm kidding -- I realize the performance issue will become more
important later when there are thousands of records in those tables.

MySQL should be able to use multiple indexes in a query. I don't know
where the comment about one index per query came from, perhaps it was
true in some ancient version of MySQL.

Try using the EXPLAIN command to get more information about its use of
indexes.
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html

Also consider there is some overhead to running two queries instead of
one. I recommend that you use the JOIN query.

Regards,
Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Pedro Alves | last post: by
1 post views Thread by Staff, Alexander | last post: by
8 posts views Thread by Andr? Queiroz | last post: by
3 posts views Thread by paul | last post: by
4 posts views Thread by Greg P | last post: by
2 posts views Thread by peter.prib | last post: by
2 posts views Thread by natG | last post: by
4 posts views Thread by crazy_jutt | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.