----- Forwarded by Peter J. Milanese/MHT/Nypl on 06/05/2003 07:35 AM -----
Peter J. Milanese
06/04/2003 09:55 PM
To: Peter J. Milanese/MHT/Nypl
cc: "Mike Hillyer" <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
Subject: RE: Suggestions on joins/merges, resolution
Greetings folks...
After a bit of thought, the solution I went with to my initial problem is:
1- determine tables necessary to perform lookup
2- merge tables into randomly generated tablename
3- run all queries on that randomly generated tablename
4- drop randomly created tablename
I do this in the reporting tool for 3 sets of tables per run, whereas all
tables within a set are identical (including indexes and such!). This adds
no overhead to the report, which is php driven.
While merge is still not view, it does the job for now... Views that are
being implemented are similar to cross dbase merges, no?
Anyhow.. Just a bit of info in followup-
P
-----Peter J. Milanese/MHT/Nypl wrote: -----
To: "Mike Hillyer" <[EMAIL PROTECTED]>
From: Peter J. Milanese/MHT/Nypl
Date: 06/02/2003 03:26PM
cc: [EMAIL PROTECTED]
Subject: RE: Suggestions on joins/merges
Well....
I was thinking about that...
However-
select sum(value) from
stats_picturecollection_nypl_org.0403_detail,stats_picturecollection_nypl_org.0503_detail,stats_picturecollection_nypl_org.0603_detail
where epoch between 1050000000 and 1054561843 and type='sessions_unk'
Also reports that 'value' is ambiguous. It is, of course. Is a join the
right way to do it? I would want the data to 'appear' in series (by row).
This is a much simpler query than the last one, and it is causing
problems.. Maybe I went down the wrong path with a join....
Any more clues?
P
"Mike Hillyer" <[EMAIL PROTECTED]>
"Mike Hillyer" <[EMAIL PROTECTED]>
06/02/2003 03:04 PM
To: "Peter J. Milanese" <[EMAIL PROTECTED]>
cc: <[EMAIL PROTECTED]>
Subject: RE: Suggestions on joins/merges
Ok...
select ref.zipcodes.state as state, count(1) as count
from
BIGTABLE,BIGTABLE2,BIGTABLE3,ref.zipcodes
where tsb between $Start and $End
and bigtable1.zipcode=ref.zipcodes.zipcode
and bigtable2.zipcode=ref.zipcodes.zipcode
and bigtable3.zipcode=ref.zipcodes.zipcode
group by state
order by count DESC
limit 10
That should do it, right?
Regards,
Mike Hillyer
www.vbmysql.com
-----Original Message-----
From: Peter J. Milanese [mailto:[EMAIL PROTECTED]
Sent: Monday, June 02, 2003 12:23 PM
To: Mike Hillyer
Cc: [EMAIL PROTECTED]
Subject: RE: Suggestions on joins/merges
Apologies.. I left that out...
The ambiguous column is 'zipcode'. it is common between all tables.
P
"Mike Hillyer" <[EMAIL PROTECTED]>
06/02/2003 01:38 PM
To: "Peter J. Milanese" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
cc:
Subject: RE: Suggestions on joins/merges
What column is MySQL complaining about being ambiguous? Isn't it just a
matter of adding tablename. To the start of the guilty column?
Mike Hillyer
www.vbmysql.com
-----Original Message-----
From: Peter J. Milanese [mailto:[EMAIL PROTECTED]
Sent: Monday, June 02, 2003 11:25 AM
To: [EMAIL PROTECTED]
Subject: Suggestions on joins/merges
Greetings:
I have a series of large tables. 5+gb each.
They have identical structures.
Sample of the query I want to run:
select ref.zipcodes.state as state, count(1) as count from
BIGTABLE,BIGTABLE2,BIGTABLE3,ref.zipcodes where tsb between $Start and
$End
and zipcode=ref.zipcodes.zipcode group by state order by count DESC
limit
10
This query references a small table (ref.zipcodes), which is not the
same
structure. I, of course, run into ambiguity issues...
Any ideas around this? It has to be a dynamic, efficient solution...
Thanks
P
--
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]
