i suck so much that i don't even know if this is a JOIN or a subquery or
who-knows what. Here's the idea:
I want to select two things at the same time (form one table)
average for columnX
and
average for columnX where columnY=Z
so i started of course with
select avg(columnX) as avg1, avg(columnX) as avg2 from table where columnY=Z
which of course gives me the same thing twice.
i want
select avg(columnX) as avg1, avg(columnX) as avg2 from table where columnY=Z
so i head in this direction:
select avg(m1.columnX) as avg1, avg(m2.columnX) as avg2 from table where
m1.columnX<>'' and m2.columnY=Z
does this sound right? I just ran it and i got two different numbers, but
-it took forever (it's a big table)
and
-i haven't checked the math by hand yet.
Any tips for speed? 4 1599
what I actually went to was
select avg(m1.columnX) as avg1, avg(m2.columnX) as avg2 from table m1,
table m2 where
m1.columnX<>'' and m2.columnY=Z
and i checked it and it looks right... i need to maximize speed though,
because i'll actually be calculating averages on several fields in this way.
How much would it slow down the inserts to build an index on EVERY field
in the table?
"Matthew Crouch" <ma************@spamlessverizon.net> wrote in message
news:iVwyf.9523$Di.3957@trnddc06... I want to select two things at the same time (form one table) average for columnX and average for columnX where columnY=Z
Here's how I'd do it:
SELECT AVG(m1.columnX) AS avg1, AVG(m2.columnX) AS avg2
FROM myTable AS m1 LEFT OUTER JOIN myTable AS m2
ON m1.primaryKey = m2.primaryKey AND m2.columnY = Z
This works because AVG() ignores rows where the field has a NULL state, and
the columnY condition in the ON clause makes the join leave out some rows of
the right-hand-side of the join, replacing them with NULL fields.
Note that if you have more computations to make, you'd make an additional
left outer join for each one. MySQL has a practical limit to the number of
joins you can do in a single query, usually 31.
Any tips for speed?
Do the separate computations in separate SQL queries. I don't know why so
many people on newsgroups insist on doing all their computations in a single
SQL query. It makes one's code a lot more complicated. Whomever takes over
the project after you're gone will curse your name.
So this is _really_ how I'd do it:
SELECT AVG(m1.columnX) AS avg1
FROM myTable
SELECT AVG(m2.columnX) AS avg2
FROM myTable AS m2
WHERE m2.columnY = Z
See how much simpler? And its easier to add new computations.
How much would it slow down the inserts to build an index on EVERY field
in the table?
That might not matter. If you need the query to be faster than it can be
without those fields indexed, then you've got to pay the cost of maintaining
the indexes (if you need both inserts and computations of averages to be at
top speed, then consider pre-calculating the averages and storing them in
another table).
You should also know that MySQL can use only one index per table in any
given query. So if it's using the index on the primary key field (e.g. for
the join), then you might as well not index columnX or columnY.
Try the queries with and without the indexes created, and use the EXPLAIN
statement to help you understand if the indexes will help or not. http://dev.mysql.com/doc/refman/5.0/en/explain.html
Regards,
Bill K.
Bill Karwin wrote: "Matthew Crouch" <ma************@spamlessverizon.net> wrote in message news:iVwyf.9523$Di.3957@trnddc06...
I want to select two things at the same time (form one table) average for columnX and average for columnX where columnY=Z
Here's how I'd do it:
SELECT AVG(m1.columnX) AS avg1, AVG(m2.columnX) AS avg2 FROM myTable AS m1 LEFT OUTER JOIN myTable AS m2 ON m1.primaryKey = m2.primaryKey AND m2.columnY = Z
Well, I was trying this out on one field, but i actually need
averages/sums for almost all of the fields in the table, so i took your
advice
this calculated 2 different averages on 8 different fields and came back
in less than a second -- 20,000 records.
This table is only likely to grow to 50,000 records over the life of the
project -- so if this checks out, i won't need any weird
optimizing/indexes at all. This is the largest table I've personally
worked with, but MySQL doesn't seem to be batting an eye...
Note that if you have more computations to make, you'd make an
additional left outer join for each one.
i didn't do this 'cause i wasn't sure what you meant, but it worked
anyway. my query was like
SELECT
avg(m1.scale) as zscale, avg(m2.scale) as pscale,
avg(m1.bf_percent) as zbf_percent, avg(m2.bf_percent) as pbf_percent,
avg(m1.scc) as zscc, avg(m2.scc) as pscc,
avg(m1.spc_agency) as zspc_agency, avg(m2.spc_agency) as pspc_agency,
avg(m1.lpc_agency) as zlpc_agency, avg(m2.lpc_agency) as plpc_agency,
avg(m1.sediment_agency) as zsediment_agency, avg(m2.sediment_agency) as
psediment_agency,
avg(m1.temp) as ztemp, avg(m2.temp) as ptemp
FROM table AS m1
LEFT OUTER JOIN table AS m2
ON m1.pkey=m2.pkey
AND m2.producer_id='34567' How much would it slow down the inserts to build an index on EVERY field
in the table?
That might not matter. If you need the query to be faster than it can be without those fields indexed, then you've got to pay the cost of maintaining the indexes (if you need both inserts and computations of averages to be at top speed, then consider pre-calculating the averages and storing them in another table).
You should also know that MySQL can use only one index per table in any given query. So if it's using the index on the primary key field (e.g. for the join), then you might as well not index columnX or columnY.
Try the queries with and without the indexes created, and use the EXPLAIN statement to help you understand if the indexes will help or not. http://dev.mysql.com/doc/refman/5.0/en/explain.html
Regards, Bill K.
"Matthew Crouch" <ma************@spamlessverizon.net> wrote in message
news:Z7zyf.8565$US3.4817@trnddc04... Note that if you have more computations to make, you'd make an additional left outer join for each one.
i didn't do this 'cause i wasn't sure what you meant, but it worked anyway.
Ah -- never mind me, I misunderstood when you said you wanted to do this on
several fields. I thought you meant that you want to calculate averages on
columnX, based on different subsets of rows. So different averages for
columnY = Z1, for columnY = Z2, for columnZ >= 1234, etc. But that's not
what you meant. :-)
Regards
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Christopher Benson-Manica |
last post by:
Is the following code legal, moral, and advisable?
#include <iostream>
class A {
private:
int a;
public:
A() : a(42) {}
|
by: Dan Vande More |
last post by:
Hey list, I'm just wondering if anyone can point me in the direction of a mirror that doesn't suck.
I generally don't do alot with postgres other than downloading and installing the newest...
|
by: MLH |
last post by:
I would like to read a MAC address from at least one NIC
in a PC on which I have an Access 97 runtime app installed.
What's the least hassel method you know of?
|
by: Ruediger Klaehn |
last post by:
Sorry about the harsh language, but I have to vent my anger to somebody who
actually understands what I am talking about. Complaining to my girlfriend
is not going to produce any meaningful results...
|
by: Dave |
last post by:
I am a VB.NET guy, recently i tried to write program using C#, having being
pampered by VB.NET's IntelliSense i find that the C#'s IntelliSense is SUCK!
|
by: wellstone9912 |
last post by:
Java programmers seem to always be whining about how confusing and
overly complex C++ appears to them. I would like to introduce an
explanation for this. Is it possible that Java programmers...
|
by: Bill Norton |
last post by:
Over at PCMag.com there is a debate going on over the usability of CSS. The
discussion was initiated by an article by John Dvorak called "Why CSS Bugs
Me"...
|
by: Slonopotam845 |
last post by:
Fat cock exploring
on this crazy movie ;)
----------------------------------
http://community.ihostasp.net
ASP.NET Developer Community
|
by: adfghergaer |
last post by:
First of all, I'm not a programmer. I'm here because I had an idea for a software program that my employer may hire someone to design, and I'm wondering what kind of investment it would take to hire...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| | |