List:General Discussion« Previous MessageNext Message »
From:Mark Phillips Date:December 14 2005 4:31pm
Subject:YAQQ (Yet Another Query Question)
View as plain text  
I am using MySQL 4.0.x on a Linux machine with a JSP/Servlet front-end to 
display the data.

I have a table with experimental data for each flight of a rocket. 
Conceptually, it looks like (with many more columns):

Flights
+-----------+----------+----------+
| flight_id | data1_id | data2_id |
+-----------+----------+----------+
|         1 |        1 |        1 |
|         2 |        1 |        3 |
|         3 |        1 |        1 |
|         4 |        2 |        2 |
|         5 |        2 |        3 |
|         6 |        1 |        1 |
|         7 |        1 |        1 |
|         8 |        4 |        4 |
|         9 |        1 |        2 |
|        10 |        1 |        2 |
|        11 |        1 |        1 |
+-----------+----------+----------+

The data1_id and data2_id are indexes for the data recorded for that flight.

I want to summarize the data. One such summary is to count the number of 
different data1_id's and data2_id's. For example:

Flight Result Summary
	    index:	1	2	3	4
data1_id		8	2	0	1
data2_id		5	3	2	1

I can think of 2 ways to make this summary table.

1. Issue 4 queries per data_id of the form 
SELECT COUNT(flight_id) FROM Flights WHERE data1_id=**
where ** is set to the values 1,2,3,4. For the table above, I would have to 
issue a total of 8 queries.

2. Issue one query of the form
SELECT flight_id FROM Flights
and do the counting in my Java code. A simple loop through the ResultSet could 
count the different values for the data_ids.

My questions are:

1. Is there a better way than these two options for getting the data I want? A 
single query per data_id? 

2. Generally, what is the most "efficient" way to do this? Is is better to 
issue more queries that gather the "calculated data" or better to issue one 
query for the raw data and then do the calculations in Java? I am sure there 
are many factors that effect the answer to this question - server resources, 
code design, etc. However, I am interested in a best practices type of answer 
or general rule of thumb from the sage experts on the list. 

Thanks for any insights you can provide!

-- 
Mark Phillips
Phillips Marketing, Inc
mark@stripped
602 524-0376
480 945-9197 fax
Thread
YAQQ (Yet Another Query Question)Mark Phillips14 Dec
  • Re: YAQQ (Yet Another Query Question)nigel wood14 Dec
    • Re: YAQQ (Yet Another Query Question)Mark Phillips14 Dec
    • Re: YAQQ (Yet Another Query Question)Mark Phillips14 Dec
  • Re: YAQQ (Yet Another Query Question)nigel wood14 Dec
    • Re: YAQQ (Yet Another Query Question)Mark Phillips14 Dec
  • Re: YAQQ (Yet Another Query Question)SGreen14 Dec
  • RE: YAQQ (Yet Another Query Question)Peter Lauri14 Dec