|
|
Posted by "Richard Lynch" on 07/01/05 01:48
On Sun, June 26, 2005 3:38 am, Pedro Quaresma de Almeida said:
> I have two databases, on for aeromodelistas (aeromodelling) and
> another for Códigos Postais (Postal Codes). I whant to do the
> following query
First, MySQL *DOES* allow you to select from multiple databases in a
single query.
Most RDBMS do not have that feature, AFAIK.
> SELECT CódigoPostal FROM Aeromodelistas
> WHERE CódigoPostal IN
> (SELECT distinct(CP4) FROM codigopostal.LOCART,codigopostal.DISTRITO
> WHERE codigopostal.LOCART.DD=codigopostal.DISTRITO.DD
> AND codigopostal.DISTRITO.DESIG='Coimbra');
>
> This query is not working, and I do not know why. If I try the two
> queries individualy they work, togheter they don't!?
As noted, maybe your MySQL version doesn't support sub-queries.
Are you using mysql_error() to find out what went wrong?
Cuz MySQL is practically BEGGING you to ask it "What went wrong?"
http://php.net/mysql_error
> But the question I want to put to the members of this list is the
> following. Is it possible to do the following?
>
> // first do the subquery
> $sql_CP4s = "select distinct(CP4) from
> codigopostal.LOCART,codigopostal.DISTRITO where
> codigopostal.LOCART.DD=codigopostal.DISTRITO.DD and
> codigopostal.DISTRITO.DESIG='$nomeDistrito'";
>
> $resultado_CP4s = mysql_query($sql_CP4s,$ligacao);
>
> $linha_CP4s = mysql_fetch_assoc($resultado_CP4s);
>
> // then use it in the main query
>
> $sql_Aero_Dist_Masc = "select count(Nome) from Aeromodelistas where
> year(AnoQuota)=2005 and Sexo='Masculino' and Distrito IN $linha_CP4s";
Yes, but...
You'll need to work on the syntax a bit.
//Add commas for valid SQL in an IN expression:
$linha_CP4s_sql = implode(", ", $inha_CP4s
//Add parens for valid SQL in an IN expression:
"... and Distrito IN ($linha_CP4s_sql) ";
> Is it possible?
Generally, it's SLOWER than using a JOIN or a sub-select.
Sometimes, particularly if the fields you are searching/joining on are not
indexed, it's actually faster.
This might be one of those cases, particularly since your Postal Codes
table probably has a LOT of entries.
For example, if you assume a rather modest number of aeromodellers (say,
2000) and a number of Postal Codes such as in the US of about 60,000 you
then have 2000 X 60,000 == 120,000,000 tuples in an unrestricted join.
Your basic $20/month webhost doesn't provide anywhere *NEAR* the amount of
temp/swap disk space you would need for 120 MILLION tuples to run AT ALL,
much less in some kind of reasonable time frame.
This could be rule-breaking time.
Normally, you would never, ever, ever want to have the same data
duplicated in two tables.
BUT, if your choice is between 120 MILLION tuples, and intelligently
de-normalizing your database, then intelligently de-normalize your
database.
Specifically:
add column to aeromoddellers DD varchar(10) default null;
(Only maybe it's int(11) instead of varchar(10) or whatever)
Write a cron job that does something like:
select id, something from aeromodellers where DD is null limit 100;
while (list($id, $something) = mysql_fetch_row($result)){
select DD from posital_coditas where something about $something
update aeromodellers set DD = $DD where id = $id
}
The purpose of the above psuedo-code is to COPY the DD you need from the
Postal Codes to the aeromodeller table.
Run the script every few minutes for a day or two, then when the table has
no NULL DD colums left, every day or so.
Add some triggers (or business logic) so that when $something changes, the
DD gets reset to NULL, and your cron job will re-copy the DD from the
Source Postal Codes.
While this is technically "nasty de-normalized data", it is done in such a
way that:
1) The DD field is never set directly, only copied from the One True Source
2) The DD field may be NULL, and un-usable, but it's never *WRONG* data.
3) Instead of 120 MILLION tuples crippling your server, you have 2000*
* 2000 is really however many rows are in aeromodeller... It's still
(1/60000)th of the search space, any way you cut it.
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|