Hi all, I'm posting this on the dev list because the experiment explorer is definitely usable, but I've got some almost identical code pulling expression data out of the database, which I think could run faster (see "EXPLAIN SELECT..." below). I also don't want to pollute the users list with code fragments and scare people away.
Here's the code (pretty much identical to http://base.thep.lu.se/browser/branches/2.5-stable/src/clients/web/net/sf/basedb/clients/web/ExperimentExplorer.java around line 730... SPOT_ALL) DynamicSpotQuery spot_query = bas.getSpotData(); spot_query.joinReporters(JoinType.INNER); spot_query.restrict( Restrictions.eq( Dynamic.reporter("id"), Expressions.integer(reporter.getId()) )); DynamicResultIterator spots = spot_query.iterate(dc); When I run the above code and grab the SQL from mysql "show processlist", and run it, it takes 1.7 seconds (there are 54 bioassays in the bioassayset). The experiment explorer GUI also takes a few seconds to go from one reporter to another. Here's the "explain". EXPLAIN SELECT `spt`.`column` AS `column`, `spt`.`position` AS `position`, `spt`.`ch1` AS `ch1`, `spt`.`ch2` AS `ch2` FROM `base2dynamicdev`.`D2Spot` `spt` INNER JOIN `base2dynamicdev`.`D2Filter` AS `flt` ON `flt`.`cube` = `spt`.`cube` AND `flt`.`column` = `spt`.`column` AND `flt`.`position` = `spt`.`position` INNER JOIN `base2dynamicdev`.`D2Pos` AS `pos` ON `pos`.`cube` = `spt`.`cube` AND `pos`.`position` = `spt`.`position` INNER JOIN `Reporters` AS rpt ON rpt.id = pos.`reporter_id` WHERE `spt`.`cube` = 3 AND `spt`.`layer` = 1 AND `flt`.`filter` = 1 AND (`rpt`.`id` = 12003)\G (look for my highlighting with '<<<<<<<<<') *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rpt type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: spt type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const,const rows: 482406 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: pos type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 6 ref: const,base2dynamicdev.spt.position rows: 1 Extra: Using where *************************** 4. row *************************** id: 1 select_type: SIMPLE table: flt type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 10 ref: const,const,base2dynamicdev.spt.column,base2dynamicdev.pos.position rows: 1 Extra: Using where; Using index I'm using BASE 2.4.5 with some indices removed during a recent upgrade (by running dropindexes.sh - I can't figure out easily if that is relevant or not right now). If there are any relevant changes in 2.5 I will make the upgrade my number 1 priority and rerun these tests. I notice also that a root bioassayset (where there is no need to query a filter layer) is not actually any quicker (also around 1.7 seconds). EXPLAIN SELECT `spt`.`column` AS `column`, `spt`.`position` AS `position`, `spt`.`ch1` AS `ch1` , `spt`.`ch2` AS `ch2` FROM `base2dynamicdev`.`D2Spot` `spt` INNER JOIN `base2dynamicdev`.`D2Pos` AS `pos` ON `pos`.`cube` = `spt`.`cube` AND `pos`.`position` = `spt`.`position` INNER JOIN `Reporters` AS rpt ON rpt.id = pos.`reporter_id` WHERE `spt`.`cube` = 3 AND `spt`.`layer` = 1 AND (`rpt`.`id` = 15295)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rpt type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: spt type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const,const rows: 482406 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Extra: *************************** 3. row *************************** id: 1 select_type: SIMPLE table: pos type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 6 ref: const,base2dynamicdev.spt.position rows: 1 Extra: Using where Does anyone else get this behaviour? many thanks, Bob. -- Bob MacCallum | VectorBase Developer | Kafatos/Christophides Groups | Division of Cell and Molecular Biology | Imperial College London | Phone +442075941945 | Email [EMAIL PROTECTED] ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ basedb-devel mailing list basedb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/basedb-devel
