Re: Another query question...
2013/11/08 17:35 -0800, Jan Steinman Okay, I think I found it: http://bugs.mysql.com/bug.php?id=47713 I added a comment with a link to a page I set up to show the behaviour on my system. http://www.ecoreality.org/wiki/WITH_ROLLUP_problem It was submitted in 2009, severity Critical, triaged Serious, and still not fixed! Yea, and the bug to which I referred is also evident in the same report, where SumQuantity is 78, and not all the rest is NULL. 'Twouldn't surprise me if the bugs are akin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
From: h...@tbbs.net 2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. Okay, I think I found it: http://bugs.mysql.com/bug.php?id=47713 I added a comment with a link to a page I set up to show the behaviour on my system. http://www.ecoreality.org/wiki/WITH_ROLLUP_problem It was submitted in 2009, severity Critical, triaged Serious, and still not fixed! The raw milk movement provides a real solution to the problem of food-borne illness -- because raw milk consumers make sure their milk comes from small, pasture-based farms and healthy animals unlikely to harbor pathogens and unlikely to contribute to water pollution, and because raw milk builds immunity to disease-causing organisms that are simply a natural part of the world in which we live. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
The plot thickens...
I noticed that I have similar queries that work as expected. The difference
appears to be that every query that is broken uses WITH ROLLUP, and removing
this makes them behave as expected.
Is this a known bug? Should I submit it as such?
If someone would be so kind as to point me to the bug system, I'll crawl around
in there to see if it's a known problem.
Jan
Begin forwarded message:
From: Jan Steinman j...@bytesmiths.com
Date: 3 November 2013 18:35:47 PST
MySQL 5.0.92-log
I'm trying to form a clickable link using CONCAT, but the link as displayed
points to the NEXT row's URL, not the one from the same row as the other data
displayed!
Is there something I don't understand about this?
Below is the query. {{{1}}} is replaced by a year, like 2013.
The second column is the problem one. When the table is displayed, the link
in the `Product` field points to the NEXT SEQUENTIAL product row! In other
words, if you click on the link for garlic, you'll get the page for
gherkins.
Live example is at: http://www.EcoReality.org/wiki/2013_harvest
If you hover over the link in the `Product` column, you can clearly see that
the page at the link is not the same as that in the `ID` column, but is in
fact the same `ID` as the next sequential row. I am so confused.
SELECT
harvest.product AS ID,
CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID,
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
FORMAT(sum(harvest.quantity), 3) AS `a
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
harvest.units AS Units,
CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
DATE(MIN(harvest.date)) AS Begin,
DATE(MAX(harvest.date)) AS End
FROM
s_product_harvest harvest
INNER JOIN
s_product on s_product.ID = harvest.product AND
s_product.units = harvest.units
LEFT OUTER JOIN
s_product_market_prices prices ON prices.product_ID = harvest.product AND
prices.units = harvest.units AND
year(prices.price_date) = year(harvest.date)
WHERE
year(harvest.date) = {{{1}}}
GROUP BY
s_product.name WITH ROLLUP
Some days I wonder if it might not be better to culturally engineer
humans to enjoy small scale garden farming than to genetically engineer weeds
to save large scale agribusiness. -- Gene Logsdon
Jan Steinman, EcoReality Co-op
The competition for grain between the wealthy car drivers of the world and
the poorest people who are trying to survive is a moral issue that we should
not ignore. The continued increase in biofuels production will result in a
continued decrease in food availability, which we could someday consider to be
a crime against humanity. -- Pat Murphy
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Another query question...
MySQL 5.0.92-log
I'm trying to form a clickable link using CONCAT, but the link as displayed
points to the NEXT row's URL, not the one from the same row as the other data
displayed!
Is there something I don't understand about this?
Below is the query. {{{1}}} is replaced by a year, like 2013.
The second column is the problem one. When the table is displayed, the link in
the `Product` field points to the NEXT SEQUENTIAL product row! In other words,
if you click on the link for garlic, you'll get the page for gherkins.
Live example is at: http://www.EcoReality.org/wiki/2013_harvest
If you hover over the link in the `Product` column, you can clearly see that
the page at the link is not the same as that in the `ID` column, but is in fact
the same `ID` as the next sequential row. I am so confused.
SELECT
harvest.product AS ID,
CONCAT('a href=http://www.EcoReality.org/wiki/Product/', s_product.ID,
'', COALESCE(s_product.name, 'TOTAL:'), '/a') AS `Product`,
FORMAT(sum(harvest.quantity), 3) AS `a
href=http://www.EcoReality.org/wiki/Harvest;Harvest/a`,
harvest.units AS Units,
CONCAT('$', FORMAT((SUM(harvest.quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
DATE(MIN(harvest.date)) AS Begin,
DATE(MAX(harvest.date)) AS End
FROM
s_product_harvest harvest
INNER JOIN
s_product on s_product.ID = harvest.product AND
s_product.units = harvest.units
LEFT OUTER JOIN
s_product_market_prices prices ON prices.product_ID = harvest.product AND
prices.units = harvest.units AND
year(prices.price_date) = year(harvest.date)
WHERE
year(harvest.date) = {{{1}}}
GROUP BY
s_product.name WITH ROLLUP
Some days I wonder if it might not be better to culturally engineer humans
to enjoy small scale garden farming than to genetically engineer weeds to save
large scale agribusiness. -- Gene Logsdon
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled Affects Me!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Another query question...
Am 04.11.2013 22:55, schrieb h...@tbbs.net: 2013/11/04 09:32 -0800, Jan Steinman I noticed that I have similar queries that work as expected. The difference appears to be that every query that is broken uses WITH ROLLUP, and removing this makes them behave as expected. Is this a known bug? Should I submit it as such? If someone would be so kind as to point me to the bug system, I'll crawl around in there to see if it's a known problem. There is a bug that I about a half year ago reported, http://bugs.mysql.com/bug.php?id=68564, that the NULL one expects with WITH ROLLUP is not always NULL, but is instead the foregoing string in the same field. I suspect that other bugs with missing NULL found by searching for ROLLUP are the same problem. (note link titled Affects Me!) Another, one year ago reported, bug of mine was handled in 5.7.2, but this one not would you please quote in a readable way instead signature.asc Description: OpenPGP digital signature
Yet another query question
Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Yet another query question
You'll need to use the technique described here: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, July 26, 2010 2:50 PM To: MySql Subject: Yet another query question Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Yet another query question
Aren't you grouping on IDt? something like ? : select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2 where t1.num=t2.num and t1.state!='new' group by t2.IDt Cheers, Geert-Jan 2010/7/26 Michael Stroh st...@astroh.org Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gbr...@gmail.com
Re: Yet another query question
Yes, sorry, you are correct. I am actually grouping on that other column. I'll take a look at this and see if it works for me. Thanks! Michael On Jul 26, 2010, at 6:10 PM, Geert-Jan Brits wrote: Aren't you grouping on IDt? something like ? : select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2 where t1.num=t2.num and t1.state!='new' group by t2.IDt Cheers, Geert-Jan 2010/7/26 Michael Stroh st...@astroh.org Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gbr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
YAQQ (Yet Another Query Question)
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_id8 2 0 1 data2_id5 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 [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Mark Phillips wrote: 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_id8 2 0 1 data2_id5 3 2 1 select sum(if(data1_id =1,1, 0)) as data1_id_1, sum(if(data1_id =2, 1, 0)) as data1_id_2, etc , etc sum(if(data2_id =1,1, 0)) as data2_id_1, sum(if(data2_id =2, 1, 0)) as data2_id_2 etc, etc from flights add composite indexes if required for speed. Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Mark Phillips wrote: 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. Sorry only just spotted the second half. Processing in MySQL will be faster than pulling the dataset back and processing it. This is particularly true if the database server is remote from the servlet container. The chief reason is that processing it on the client add the time needed to copy the raw data over the network. In Java or C.* data processing performance can be on a par with MySQL once the data is obtained, against an interpreted language such as PHP or Perl the database's performance will always win hands down even if temporary tables are needed. If the rocket data doesn't change rapidly the MySQL query cache will also improve preformance. This feature speeds things by remembering the answer to your query and replying with a cached version until the rockets table is next updated. Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Mark Phillips [EMAIL PROTECTED] wrote on 12/14/2005 11:31:03 AM: 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 dataI 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 [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax Your option 1) may experience network lag for each query/result cycle, depending on how you connect. If you have a decent index, each query will be very quick so that's not necessarily going to be much of an issue. If you have a fast connection that becomes less of an issue, too. Your option 2) could turn out to be very quick, it all depends on how efficiently you can code your pivot routine on the client side. I thought this was going to be a simple pivot table until I looked again. You are actually pivoting your data twice: Once around the flight_id to put your column headers as the row headers, and the second time to convert discreet column values into column headers. A single pivot can be rather quick under most circumstances but this double pivot would be a rather ungainly SQL statement and would not actually save you much effort (unless you automated its production in your application's code). It's a fairly easy pattern to write but by the time you wrote the query and executed it, you could have taken the raw data and transformed it just as easily using your option 2). This is one of those situations where the data transformation is best left to application-layer code (using loops and arrays) than it would be to try to create a SQL statement to do it at the server. IMHO, Stick with 2). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: YAQQ (Yet Another Query Question)
Have you tried the GROUP BY? Make something like (not sure of exact syntax, check the manual for that): SELECT COUNT(*) AS cnt, data1_id FROM data1_id GROUP BY data1_iD; /Peter -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 14, 2005 11:31 PM To: MYSQL List Subject: YAQQ (Yet Another Query Question) 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_id8 2 0 1 data2_id5 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 [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Nigel, Thanks! Mark On Wednesday 14 December 2005 09:42 am, nigel wood wrote: Mark Phillips wrote: 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 select sum(if(data1_id =1,1, 0)) as data1_id_1, sum(if(data1_id =2, 1, 0)) as data1_id_2, etc , etc sum(if(data2_id =1,1, 0)) as data2_id_1, sum(if(data2_id =2, 1, 0)) as data2_id_2 etc, etc from flights add composite indexes if required for speed. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Nigel, Again, thanks - that is the rule of thumb I was looking for! Mark On Wednesday 14 December 2005 09:57 am, nigel wood wrote: Mark Phillips wrote: 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. Sorry only just spotted the second half. Processing in MySQL will be faster than pulling the dataset back and processing it. This is particularly true if the database server is remote from the servlet container. The chief reason is that processing it on the client add the time needed to copy the raw data over the network. In Java or C.* data processing performance can be on a par with MySQL once the data is obtained, against an interpreted language such as PHP or Perl the database's performance will always win hands down even if temporary tables are needed. If the rocket data doesn't change rapidly the MySQL query cache will also improve preformance. This feature speeds things by remembering the answer to your query and replying with a cached version until the rockets table is next updated. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: YAQQ (Yet Another Query Question)
Thanks to everyone for their help. Using Nigel's suggestion, I was able to gather all the summary data in one query. Those nested if()'s are really useful! FWIW, you can see the summary stats at http://rockets.phillipsoasis.com Just click on Hopi Rockets and scroll to the bottom of the page. My small contribution to science education! This list is great! Mark On Wednesday 14 December 2005 09:42 am, nigel wood wrote: Mark Phillips wrote: 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 select sum(if(data1_id =1,1, 0)) as data1_id_1, sum(if(data1_id =2, 1, 0)) as data1_id_2, etc , etc sum(if(data2_id =1,1, 0)) as data2_id_1, sum(if(data2_id =2, 1, 0)) as data2_id_2 etc, etc from flights add composite indexes if required for speed. Nigel -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
