<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">Dear EnsEMBLers,<div><br></div><div>We have installed a local version of the Compara database (version 73) and one of our goal is to rapidly extract ortholog informations (<font face="Courier New">stable_id, chr_name, chr_start, chr_end</font>) between 2 species using mysql query. (I believe this topic has already been discussed but only via the API)</div><div><br></div><div>I used this example query to get ortholog stable_ids information between human and mouse:</div><div><br></div><div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">mysql> SELECT m1.stable_id, m2.stable_id</font></div><div style="margin: 0px;"><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> FROM genome_db gdb1</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN member m1 USING (genome_db_id)</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN homology_member hm1 USING (member_id)</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN homology using (homology_id)</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN homology_member hm2 using (homology_id)</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN member m2 on (hm2.member_id = m2.member_id)</font></span></div><div style="margin: 0px;"><span style="font-size: 11px;"><font face="Courier New"> LEFT JOIN genome_db gdb2 on (m2.genome_db_id = gdb2.genome_db_id)</font></span></div><div style="margin: 0px;"><font face="Courier New"><span style="font-size: 11px;"> WHERE gdb1.name = 'homo_sapiens' and gdb2.name = '</span><span style="font-size: 11px;">mus_musculus</span><span style="font-size: 11px;">' LIMIT 1;</span></font></div></div></div><div><font face="Courier New"><br></font></div><div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">+-----------------+--------------------+</font></div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">| stable_id | stable_id |</font></div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">+-----------------+--------------------+</font></div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">| ENSG00000198888 | ENSMUSG00000064341 | </font></div><div style="margin: 0px; font-size: 11px;"><font face="Courier New">+-----------------+--------------------+</font></div><div style="margin: 0px; font-size: 11px;"><div style="margin: 0px; font-family: Menlo;">1 row in set <b>(24.79 sec</b>)</div><div><br></div></div></div><div><br></div><div>As you can see, it takes quite a bit of time using this query (which is also the case when I connect to <span style="font-family: Menlo; font-size: 11px;"><a href="http://ensembldb.ensembl.org">ensembldb.ensembl.org</a>)</span>.</div><div>Do you know if there is a clever (thus faster) way to extract our desired output? Do we need to go through the <font face="Courier New">method_link_species_set</font> table?</div><div><br></div><div>Thanks in advance for your help!</div><div><br></div><div>All the best,</div><div><br></div><div>Thomas</div></body></html>