id summary reporter owner description type status priority milestone component version resolution keywords cc 903 Experiment Explorer is slow base Nicklas Nordborg "Here's some code which is 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) {{{ +----+-------------+-------+--------+---------------+---------+---------+---------------------------------------------------------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------------------------------------------------------------+--------+--------------------------+ | 1 | SIMPLE | rpt | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | spt | ref | PRIMARY | PRIMARY | 4 | const,const | 482406 | | | 1 | SIMPLE | pos | eq_ref | PRIMARY | PRIMARY | 6 | const,base2dynamicdev.spt.position | 1 | Using where | | 1 | SIMPLE | flt | eq_ref | PRIMARY | PRIMARY | 10 | const,const,base2dynamicdev.spt.column,base2dynamicdev.pos.position | 1 | 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) {{{ +----+-------------+-------+--------+---------------+---------+---------+------------------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------------------------+--------+-------------+ | 1 | SIMPLE | rpt | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | spt | ref | PRIMARY | PRIMARY | 4 | const,const | 482406 | | | 1 | SIMPLE | pos | eq_ref | PRIMARY | PRIMARY | 6 | const,base2dynamicdev.spt.position | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+------------------------------------+--------+-------------+ }}} Does anyone else get this behaviour? many thanks, Bob. " enhancement closed major BASE 2.11 core fixed