473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1774
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Pedro Alves | last post by:
Hi. I'm having some trouble on the use of indexes. The querys below are exactly the same but refer to different months. One case uses indexes, the other doesn't. Is there anything I can do?...
1
by: Staff, Alexander | last post by:
Hi, I created a simple table (name char200, zip char10, city char200, street char200, id int) and filled some data, appr. 250 000 records, in it. I tested accessing some rows (select id from...
8
by: Andr? Queiroz | last post by:
Hi, I have a table with 10M records and col A has a index created on it. The data on that table has the same value for col A on all 10M records. After that I insert diferent values for that column...
8
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is...
3
by: paul | last post by:
Hi, is there any way in db2 v8.2.3 (fp10) on aix 5.3 to know if indexes on table are used. The only method i know is to create an event monitor on statement and then pass this worload to the...
4
by: Greg P | last post by:
If i connect to my database and select all tables and views. Then I drag a few differnt tables onto differnt tabs on the same form. I know that the data isn't bound until the tab header is...
2
by: peter.prib | last post by:
Hi, I am having trouble getting DB2 to select an index I have created for a spatial location. Table definition create table gps_paf_address ( gnaf_address gnaf_address not null...
2
by: natG | last post by:
On a 3 column table, the PK consists of all 3 columns. I have queries on the first two columns, as well as on the last two columns. (Select * where column3=x and column2=y.) I was hoping that...
4
by: crazy_jutt | last post by:
hi all, i heard that db2 ignores indexes when using any column function on the column which has index on it. but i have seen db2 using indexes even when using column function. what is the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.