Database benchmarking
From ICISWiki
Contents |
ICIS WEB (new generation)
Rice
Frequently used SQLs:
Note: koios.generationcp.org has shown improved performance during the September 2 testing
| Use case | SQL | Number of records | Test date | MySQL-MyISAM (koios.
generationcp. org) | MySQL-MyISAM (ASTI server) | MySQL-InnoDB (ASTI server) | PostgreSQL (ASTI server) | ASTI IP used |
|---|---|---|---|---|---|---|---|---|
| Get StudyEffects By Study ID | SELECT DISTINCT e.effectid, s.sname, s.studyid FROM study s, effect e, factor f WHERE s.studyId = 586 and s.studyId = f.studyId and e.factorid = f.factorid | 7 | Aug 21, 2008 | 0.77 sec | 0.25 sec | 0.25 sec | 0.27 sec | (internal only) |
| September 2, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.50 sec | (external) | |||
| Get StudyEffects By Study Name using LIKE | SELECT DISTINCT e.effectid, s.sname, s.studyid FROM study s, effect e, factor f WHERE s.sname like 'GCP_%' and s.studyId = f.studyId and e.factorid = f.factorid | 10 | Aug 21, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.27 sec | (internal only) |
| September 2, 2008 | 0.25 sec | 0.25 sec | 0.27 sec | 0.25 sec | (external) | |||
| Get StudyEffects By Study ID | SELECT DISTINCT e.effectid, s.sname, s.studyid FROM study s, effect e, factor f WHERE s.studyId = 586 and s.studyId = f.studyId and e.factorid = f.factorid | 7 | Aug 21, 2008 | 0.77 sec | 0.25 sec | 0.25 sec | 0.27 sec | (internal only) |
| September 2, 2008 | 0.27 sec | 0.25 sec | 0.25 sec | 0.25 sec | (external) | |||
| Get Study Observation Units By EffectId AND LevelNo | SELECT DISTINCT ou.ounitid FROM oindex ou, effect e WHERE e.effectid=1532 and ou.levelno=225503 and e.factorid=ou.factorid and e.represno=ou.represno | 1 | Aug 21, 2008 | 0.25 sec | 0.27 sec | 0.27 sec | 0.25 sec | (internal only) |
| September 2, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.50 sec | (external) | |||
| Get Values by Factor ID | MySQL:
SELECT f.factorid, f.labelid, f.fname, CAST(ln.lvalue AS CHAR), ln.levelno, f.ltype FROM factor f, level_n ln WHERE f.labelid = ln.labelid AND f.labelid = 6874 UNION SELECT f.factorid, f.labelid, f.fname,lc.lvalue, lc.levelno, f.ltype FROM factor f, level_c lc WHERE f.labelid = lc.labelid AND f.labelid = 6874 PostgreSQL: SELECT f.factorid, f.labelid, f.fname, CAST(ln.lvalue AS TEXT), ln.levelno, f.ltype FROM factor f, level_n ln WHERE f.labelid = ln.labelid AND f.labelid = 6874 UNION SELECT f.factorid, f.labelid, f.fname,lc.lvalue, lc.levelno, f.ltype FROM factor f, level_c lc WHERE f.labelid = lc.labelid AND f.labelid = 6874 | 2668 | Aug 21, 2008 | 0.27 sec | 0.25 sec | 0.25 sec | 0.75 sec | (internal only) |
| September 2, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.27 sec | (external) | |||
| Get Values by Variate ID | MySQL:
SELECT v.variatid, v.vname, CAST(dn.dvalue AS CHAR), v.dtype FROM variate v, data_n dn WHERE v.variatid = dn.variatid AND v.variatid = 3 UNION SELECT v.variatid, v.vname, dc.dvalue, v.dtype FROM variate v, data_c dc WHERE v.variatid = dc.variatid AND v.variatid = 3 PostgreSQL: SELECT v.variatid, v.vname, CAST(dn.dvalue AS TEXT), v.dtype FROM variate v, data_n dn WHERE v.variatid = dn.variatid AND v.variatid = 3 UNION SELECT v.variatid, v.vname, dc.dvalue, v.dtype FROM variate v, data_c dc WHERE v.variatid = dc.variatid AND v.variatid = 3 | 62 | Aug 21, 2008 | 0.52 sec | 0.25 sec | 0.27 sec | 0.27 sec | (internal only) |
| September 2, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.27 sec | (external) | |||
| Get TabularData By Row LevelNo, Column LevelNo and Variate ID |
SELECT z.ounitid, z.levelrow, x.levelcolumn, z.dvalue FROM (SELECT o.ounitid, o.levelno AS levelrow, d.dvalue FROM oindex o, data_n d WHERE o.ounitid = d.ounitid AND levelno IN (294677, 294678) AND d.variatid = 28514 ) AS z, (SELECT ounitid, levelno AS levelcolumn FROM oindex WHERE levelno IN (294985, 294986)) AS x WHERE z.ounitid=x.ounitid | 4 | Aug 21, 2008 | 0.25 sec | 0.25 sec | 0.25 sec | 0.25 sec | (internal only) |
| September 2, 2008 | 0.50 sec | 0.25 sec | 0.25 sec | 0.50 sec | (external) | |||
| INNER JOIN germplsm and names tables | select * from germplsm as g inner join names as n on g.gid = n.gid | September 2, 2008 | 42.27 sec | 33.00 sec | 7.50 sec | 14.02 sec | (external) |
Slow-performing SQLs:
Note: koios.generationcp.org has shown improved performance during the September 2 testing
| Use case | SQL | Number of records | Test date | MySQL-MyISAM (koios.generationcp) | MySQL-MyISAM (ASTI server) | MySQL-InnoDB (ASTI server) | PostgreSQL (ASTI server) | ASTI IP used |
|---|---|---|---|---|---|---|---|---|
| Get Detected Variants By StudyID | SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker
FROM gems_mv gmv, gems_names gnames1, gems_names gnames2, variate v INNER JOIN data_n dn ON v.variatid = dn.variatid WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND v.scaleid=503 AND v.studyId = 638 | 107 | August 21, 2008 | 60.30 sec | 14.50 sec | 25.28 sec | 0.52 sec | (internal only) |
| September 2, 2008 | 0.75 sec | 0.77 sec | 0.52 sec | 0.50 sec | (external) | |||
| Get DetectedVariants By StudyName |
SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker FROM gems_mv gmv, gems_names gnames1, gems_names gnames2, (SELECT v.variatid, v.scaleid, y.sname FROM study y INNER JOIN variate v ON y.studyid = v.studyid WHERE y.stype = 'G') AS s INNER JOIN data_n dn ON s.variatid = dn.variatid WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND s.scaleid=503 AND s.sname = 'GCP_WARDA' | 107 | August 21, 2008 | 56.27 sec | 15.30 sec | 26.31 sec | 0.50 sec | (internal only) |
| September 2, 2008 | 0.75 sec | 0.75 sec | 0.52 sec | 0.53 sec | (external) | |||
| Get DetectedVariants By StudyName using “LIKE” Operator | SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker
FROM gems_mv gmv, gems_names gnames1, gems_names gnames2, (SELECT v.variatid, v.scaleid, y.sname FROM study y INNER JOIN variate v ON y.studyid = v.studyid WHERE y.stype = 'G') AS s INNER JOIN data_n dn ON s.variatid = dn.variatid WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND s.scaleid=503 AND s.sname like 'GCP_WARDA%' | 107 | August 21, 2008 | 64.41 sec | 15.52 sec | 26.03 sec | 0.50 sec | (internal only) |
| September 2, 2008 | 0.52 sec | 0.78 sec | 0.50 sec | 0.50 sec | (external) | |||
| Get Detected Variants By Germplasm ID |
SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker FROM gems_mv gmv, study s, gems_names gnames1, gems_names gnames2, variate v INNER JOIN data_n dn ON v.variatid = dn.variatid, (factor f1 INNER JOIN level_n ln ON f1.labelid = ln.labelid) INNER JOIN oindex ou1 ON ln.levelno = ou1.levelno WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND v.studyid = s.studyid AND s.stype = 'G' AND ou1.ounitid = dn.ounitid AND f1.scaleid=91 AND v.scaleid=503 AND ln.lvalue = -4620 | 50 | August 21, 2008 | 0.75 sec | 0.25 sec | 0.25 sec | 0.75 sec | (internal only) |
| September 2, 2008 | 0.27 sec | 0.25 sec | 0.25 sec | 1.03 sec | (external) | |||
| Get Detected Variants By Germplasm Name |
SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker FROM gems_mv gmv, study s, gems_names gnames1, gems_names gnames2, variate v INNER JOIN data_n dn ON v.variatid = dn.variatid, ((factor f1 INNER JOIN level_n ln ON f1.labelid = ln.labelid) INNER JOIN oindex ou1 ON ln.levelno = ou1.levelno) INNER JOIN ((oindex ou2 INNER JOIN level_c lc ON ou2.levelno = lc.levelno) INNER JOIN factor f2 ON lc.labelid = f2.labelid) ON ou1.ounitid = ou2.ounitid WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND v.studyid = s.studyid AND s.stype = 'G' AND ou2.ounitid = dn.ounitid AND (((f2.scaleid)=92) AND ((f1.scaleid)=91)) AND v.scaleid=503 AND lc.lvalue = 'ZS004' | 50 | August 21, 2008 | 81.78 sec | 33.39 sec | 1.25 sec | 1.50 sec | (internal only) |
| September 2, 2008 | 0.52 sec | 0.25 sec | 0.27 sec | 2.25 sec | (external) | |||
| Get Detected Variants By Germplasm Name Using “LIKE” Operator |
SELECT DISTINCT gmv.mvid, gnames1.gnval as allele, gmv.mdid, gnames2.gnval as marker FROM gems_mv gmv, study s, gems_names gnames1, gems_names gnames2, variate v INNER JOIN data_n dn ON v.variatid = dn.variatid, ((factor f1 INNER JOIN level_n ln ON f1.labelid = ln.labelid) INNER JOIN oindex ou1 ON ln.levelno = ou1.levelno) INNER JOIN ((oindex ou2 INNER JOIN level_c lc ON ou2.levelno = lc.levelno) INNER JOIN factor f2 ON lc.labelid = f2.labelid) ON ou1.ounitid = ou2.ounitid WHERE gnames1.gobjtype='gems_mv' AND gnames2.gobjtype='gems_marker_detector' AND gnames1.gobjid=gmv.mvid AND gnames2.gobjid=gmv.mdid AND dn.dvalue=gmv.mvid AND v.studyid = s.studyid AND s.stype = 'G' AND ou2.ounitid = dn.ounitid AND (((f2.scaleid)=92) AND ((f1.scaleid)=91)) AND v.scaleid=503 AND lc.lvalue like 'ZS004%' | 50 | August 21, 2008 | 66.78 sec | 33.06 sec | 1.02 sec | 1.00 sec | (internal only) |
| September 2, 2008 | 0.27 sec | 0.27 sec | 0.25 sec | 1.01 sec | (external) |
CROPFINDER
Maize
| Query | SQL | Number of records | Test date | MySQL-MyISAM (koios.generationcp.org) | MySQL-MyISAM (ASTI server) | MySQL-InnoDB (ASTI server) |
|---|---|---|---|---|---|---|
| A. |
select distinct study.sname, data_n_anthesisdate_days.dvalue as anthesisdate_days, factors.gid, data_n_grainyieldkg_kg.dvalue as grainyieldkg_kg, data_n_plantheight_cm.dvalue as plantheight_cm, factors.management, factors.collaborator, factors.maizeid, factors.nameofexperiment, factors.year, factors.locationid, factors.breedingprogram, factors.pedigree, factors.experimentid from data_n data_n_anthesisdate_days inner join data_n data_n_grainyieldkg_kg on data_n_anthesisdate_days.ounitid = data_n_grainyieldkg_kg.ounitid and data_n_grainyieldkg_kg.variatid in (select distinct variatid from variate where traitid = 1014 and scaleid = 1014) and data_n_grainyieldkg_kg.dvalue >= 6000 inner join data_n data_n_plantheight_cm on data_n_anthesisdate_days.ounitid = data_n_plantheight_cm.ounitid and data_n_plantheight_cm.variatid in (select distinct variatid from variate where traitid = 1008 and scaleid = 1008) and data_n_plantheight_cm.dvalue <= 180 inner join factors on factors.ounitid = data_n_anthesisdate_days.ounitid inner join study on study.studyid= factors.studyid where factors.breedingprogram like 'cimmyt-zimbabwe' and data_n_grainyieldkg_kg.dvalue >= 6000 and data_n_plantheight_cm.dvalue <= 180 and data_n_anthesisdate_days.variatid in (select distinct variatid from variate where traitid = 1001 and scaleid = 1001) | 4227 | August 29, 2008 | 7.28 sec | 16.30 sec | 8.50 sec |
| September 2, 2008 | 7.52 sec | 16.75 sec | 8.75 sec | |||
| B. (column factors.`entry number` included in query) |
select distinct study.sname, data_n_anthesisdate_days.dvalue as anthesisdate_days, factors.gid, data_n_grainyieldkg_kg.dvalue as grainyieldkg_kg, data_n_plantheight_cm.dvalue as plantheight_cm, factors.management, factors.collaborator, factors.maizeid, factors.nameofexperiment, factors.`entry number`, factors.year, factors.locationid, factors.breedingprogram, factors.pedigree, factors.experimentid from data_n data_n_anthesisdate_days inner join data_n data_n_grainyieldkg_kg on data_n_anthesisdate_days.ounitid = data_n_grainyieldkg_kg.ounitid and data_n_grainyieldkg_kg.variatid in (select distinct variatid from variate where traitid = 1014 and scaleid = 1014) and data_n_grainyieldkg_kg.dvalue >= 6000 inner join data_n data_n_plantheight_cm on data_n_anthesisdate_days.ounitid = data_n_plantheight_cm.ounitid and data_n_plantheight_cm.variatid in (select distinct variatid from variate where traitid = 1008 and scaleid = 1008) and data_n_plantheight_cm.dvalue <= 180 inner join factors on factors.ounitid = data_n_anthesisdate_days.ounitid inner join study on study.studyid= factors.studyid where factors.breedingprogram like 'cimmyt-zimbabwe' and data_n_grainyieldkg_kg.dvalue >= 6000 and data_n_plantheight_cm.dvalue <= 180 and data_n_anthesisdate_days.variatid in (select distinct variatid from variate where traitid = 1001 and scaleid = 1001) | 4227 | September 4, 2008 | 8.27 sec | 8.30 sec | |
Wheat
Query optimization # 1
Best slowest times highlighted in yellow
| Query | SQL | Number of records | Test date | MySQL-MyISAM (koios.generationcp.org) | MySQL-MyISAM (ASTI server) |
|---|---|---|---|---|---|
| A. (filter by studyid) | Select distinct `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) and `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid WHERE `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 and `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) and factors.studyid in (6,1) | 17 | September 2, 2008 | 0.25 sec | 0.27 sec |
| B. (A with no filter). | Select distinct `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) and `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid WHERE `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 and `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) | 332,127 | September 4, 2008 | 234.25 sec (~4 mins) | 140.34 sec (~2 mins) |
| C. (B rewritten; put conditions under WHERE clause). | Select distinct `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid WHERE `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) and `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 | 332,127 | September 4, 2008 | 304.52 sec (~5 mins) | 294.50 sec (~5 mins) |
| D. (C rewritten; no WHERE clause). | Select distinct `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid and `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) and `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid | 332,127 | September 4, 2008 | 254.88 sec (~4 mins) | 159.50 sec (~3 mins) |
| E. (D rewritten; "IN" clause within inner join, ">,<=" within WHERE clause). | Select distinct `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid and `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid WHERE `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 | 332,127 | September 4, 2008 | 230.28 sec (~4 mins) | 134.25 sec (~2 mins) |
| F. (E rewritten; use GROUP BY instead of DISTINCT). | Select `data_n_GRAIN_YIELD_t/ha`.dvalue as `GRAIN_YIELD_t/ha`,
`data_n_PLANT_HEIGHT_cm`.dvalue as `PLANT_HEIGHT_cm`, `factors`.`TID` as `TID`, `factors`.`CROSS NAME` as `CROSS NAME`, `factors`.`GID` as `GID`, `factors`.`SELECTION HISTORY` as `SELECTION HISTORY`, `study`.`SNAME` as `SNAME` FROM data_n `data_n_GRAIN_YIELD_t/ha` inner join data_n `data_n_PLANT_HEIGHT_cm` ON `data_n_GRAIN_YIELD_t/ha`.ounitid = `data_n_PLANT_HEIGHT_cm`.ounitid and `data_n_GRAIN_YIELD_t/ha`.variatid in (select distinct variatid from variate where traitid = 1144 and scaleid = 20651) and `data_n_PLANT_HEIGHT_cm`.variatid in (select distinct variatid from variate where traitid = 1151 and scaleid = 20195) inner join factors on factors.ounitid= `data_n_GRAIN_YIELD_t/ha`.ounitid inner join study on factors.studyid = study.studyid WHERE `data_n_GRAIN_YIELD_t/ha`.`dvalue` > 5 AND `data_n_PLANT_HEIGHT_cm`.`dvalue` <= 150 GROUP BY `data_n_GRAIN_YIELD_t/ha`.dvalue, `data_n_PLANT_HEIGHT_cm`.dvalue, `factors`.`TID`, `factors`.`CROSS NAME`, `factors`.`GID`, `factors`.`SELECTION HISTORY`, `study`.`SNAME` | 332,127 | September 4, 2008 | 484.85 sec (~8 mins) | 290.00 sec (~5 mins) |
