<html><head><meta http-equiv="Content-Type" content="text/html charset=windows-1252"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">Dear Miguell,<div><br></div><div>Thank you very much for your answer!</div><div>The second query is by far quicker than the first one (and also compared to what I did before) although I don’t have exactly the same length of time than you i.e</div><div><div style="margin: 0px; font-size: 11px; font-family: Menlo;">25166 rows in set (3 min 49.53 sec)</div></div><div style="margin: 0px; font-size: 11px; font-family: Menlo;"><br></div><div style="margin: 0px;">But, I willl definitely use the way you extract ortholog information. Just by curiosity, did you index some specific fields that could explain the query time difference?</div><div style="margin: 0px;"><br></div><div style="margin: 0px;">Thanks again!</div><div style="margin: 0px;"><br></div><div style="margin: 0px;">Thomas</div><div style="margin: 0px;"><br></div><div style="margin: 0px; font-size: 11px; font-family: Menlo;"><br></div><div><br><div><div>Le 4 déc. 2013 à 17:47, Miguel Pignatelli <<a href="mailto:mp@ebi.ac.uk">mp@ebi.ac.uk</a>> a écrit :</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;">Hi Thomas,<br><br>You can use the method_link_species_set table to get the orthologues you want:<br><br>SELECT homology.description, GROUP_CONCAT(stable_id) FROM homology JOIN homology_member USING(homology_id) JOIN member USING(member_id) JOIN method_link_species_set USING(method_link_species_set_id) WHERE name = 'H.sap-M.mus orthologues' GROUP BY homology_id;<br><br>The query will be faster if you get include the method_link_species_set_id directly:<br><br>SELECT homology.description, GROUP_CONCAT(stable_id) FROM homology JOIN homology_member USING(homology_id) JOIN member USING(member_id) WHERE method_link_species_set_id = 29697 GROUP BY homology_id;<br>(25271 rows in 0.42 sec)<br><br>Hope this helps,<br><br>Cheers,<br><br>M;<br><br><br>On 04/12/13 15:24, Thomas Derrien wrote:<br><blockquote type="cite">Dear EnsEMBLers,<br><br>We have installed a local version of the Compara database (version 73)<br>and one of our goal is to rapidly extract ortholog informations<br>(stable_id, chr_name, chr_start, chr_end) between 2 species using mysql<br>query. (I believe this topic has already been discussed but only via the<br>API)<br><br>I used this example query to get ortholog stable_ids information between<br>human and mouse:<br><br>mysql> SELECT m1.stable_id, m2.stable_id<br> FROM genome_db gdb1<br> LEFT JOIN member m1 USING (genome_db_id)<br> LEFT JOIN homology_member hm1 USING (member_id)<br> LEFT JOIN homology using (homology_id)<br> LEFT JOIN homology_member hm2 using (homology_id)<br> LEFT JOIN member m2 on (hm2.member_id = m2.member_id)<br> LEFT JOIN genome_db gdb2 on (m2.genome_db_id = gdb2.genome_db_id)<br> WHERE gdb1.name = 'homo_sapiens' and gdb2.name = 'mus_musculus'<br>LIMIT 1;<br><br>+-----------------+--------------------+<br>| stable_id | stable_id |<br>+-----------------+--------------------+<br>| ENSG00000198888 | ENSMUSG00000064341 |<br>+-----------------+--------------------+<br>1 row in set *(24.79 sec*)<br><br><br>As you can see, it takes quite a bit of time using this query (which is<br>also the case when I connect to <a href="http://ensembldb.ensembl.org">ensembldb.ensembl.org</a><br><<a href="http://ensembldb.ensembl.org/">http://ensembldb.ensembl.org</a>>).<br>Do you know if there is a clever (thus faster) way to extract our<br>desired output? Do we need to go through the method_link_species_set table?<br><br>Thanks in advance for your help!<br><br>All the best,<br><br>Thomas<br><br><br>_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info:<span class="Apple-converted-space"> </span><a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog:<span class="Apple-converted-space"> </span><a href="http://www.ensembl.info/">http://www.ensembl.info/</a><br><br></blockquote><br>--<span class="Apple-converted-space"> </span><br><br>Miguel Pignatelli, PhD<br><br>Ensembl Developer - Comparative Genomics<br>European Bioinformatics Institute (EMBL-EBI)<br>Wellcome Trust Genome Campus, Hinxton<br>Cambridge - CB10 1SD - UK<br>Room A3-33<br>Phone + 44 (0) 1223 494 598<br>Fax + 44 (0) 1223 494 468</div></blockquote></div><br></div></body></html>