Hi,
I have a database like this:
CREATE TABLE weersgeschiedenis (
WMO enum('06235','06260','06280','06290','06310','0638 0') NOT NULL default
'06235',
YYYYMMDD date NOT NULL default '0000-00-00',
DDVEC smallint(5) unsigned NOT NULL default '0',
FG tinyint(3) unsigned NOT NULL default '0',
FHX smallint(5) unsigned NOT NULL default '0',
FX smallint(5) unsigned NOT NULL default '0',
TG smallint(6) NOT NULL default '0',
TN smallint(6) NOT NULL default '0',
TX smallint(6) NOT NULL default '0',
SQ smallint(6) NOT NULL default '0',
SP tinyint(3) unsigned NOT NULL default '0',
DR tinyint(3) unsigned NOT NULL default '0',
RH smallint(6) NOT NULL default '0',
PG smallint(5) unsigned NOT NULL default '0',
VVN tinyint(3) unsigned NOT NULL default '0',
NG tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (WMO,YYYYMMDD)
) TYPE=MyISAM
For one page I need to have all of the following 8 results:
SELECT WMO,YYYYMMDD,DDVEC,FG,FHX,FX FROM $dbtable WHERE
year(YYYYMMDD)='$year' ORDER BY FX DESC,FHX DESC,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,DR,RH FROM $dbtable WHERE year(YYYYMMDD)='$year' ORDER
BY RH DESC,DR DESC,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,TG,TN,TX FROM $dbtable WHERE year(YYYYMMDD)='$year'
ORDER BY TG,TN,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,TN FROM $dbtable WHERE year(YYYYMMDD)='$year' ORDER BY
TN,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,TG,SQ,SP FROM $dbtable WHERE year(YYYYMMDD)='$year'
ORDER BY SP DESC,SQ DESC,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,TG,TN,TX FROM $dbtable WHERE year(YYYYMMDD)='$year'
ORDER BY TG DESC,TX DESC,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,TX FROM $dbtable WHERE year(YYYYMMDD)='$year' ORDER BY
TX DESC,YYYYMMDD DESC LIMIT 1
SELECT WMO,YYYYMMDD,
(1.41-(0.1162*FG)+(0.0980*TG)+(0.000124*FG*FG)+(0.000185 *FG*TG)) as TEMP
FROM $dbtable WHERE year(YYYYMMDD)='$year' ORDER BY TEMP ASC, YYYYMMDD DESC
LIMIT 1
The total query time is now about 2.10 seconds.
$year is a variable that can be 1901 till 2004.
If you have any tips on how to speed up this query's (maybe by combining?)
please post a reply :)
Thanks,
Wouter