[ensembl-dev] Affymetrix Probesets

[Alexander Pico]

Remarkable...

After running both OPTIMIZE statements, I now get the exact same "explain"
output as you guys (see below). And the formerly problematic lines now
breeze by in seconds (rather than minutes):

$probe_feature_adaptor->fetch_all_by_linked_transcript_Gene($gene);
-or-
$probe_feature_adaptor->fetch_all_by_external_name('ENSMUST00000102049');

This is fantastic.  Should I be running OPTIMIZE on these two tables for
every species and every release?  Is it common practice to OPTIMIZE all
tables over a certain size?  A quick Google search seem to suggest that this
is only needed after making major changes (e.g., deletions), but these
tables are fresh from your ftp.

 - Alex


+----+-------------+-------+-------+-------------------+---------+---------+
-----------------------------------------+------+---------------------------
--+
| id | select_type | table | type  | possible_keys     | key     | key_len |
ref                                     | rows | Extra
|
+----+-------------+-------+-------+-------------------+---------+---------+
-----------------------------------------+------+---------------------------
--+
|  1 | SIMPLE      | pf    | range | PRIMARY,probe_idx | PRIMARY | 4       |
NULL                                    | 2048 | Using where; Using filesort
| 
|  1 | SIMPLE      | p     | ref   | PRIMARY           | PRIMARY | 4       |
mus_musculus_funcgen_62_37o.pf.probe_id |    1 |
| 
+----+-------------+-------+-------+-------------------+---------+---------+
-----------------------------------------+------+---------------------------
--+



On 7/4/11 1:46 PM, "Ian Sealy" <is1 at sanger.ac.uk> wrote:

> Dear Alex,
> 
>>> Also, have you tried optimising the tables?
>> 
>> You mentioned optimizing the tables? I simply installed databases from
>> Ensembl, the corresponding API and then started running my scripts.
>> Is "optimizing" a command I run on certain tables?
> 
> Yep, I think Nathan is suggesting you try:
> 
> OPTIMIZE TABLE probe_feature;
> OPTIMIZE TABLE probe;
> 
> See http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html for what
> it does.
> 
> Running ANALYZE TABLE might be enough though.
> 
> Cheers,
> Ian
>