mysql> select ensembl_compara_86.gene_member.gene_member_id, ensembl_compara_86.gene_member.taxon_id, homo_sapiens_core_86_38.gene.stable_id, homo_sapiens_core_86_38.gene.biotype from homo_sapiens_core_86_38.gene INNER JOIN ensembl_compara_86.gene_member USING (stable_id) limit 5; +----------------+----------+-----------------+----------+ | gene_member_id | taxon_id | stable_id | biotype | +----------------+----------+-----------------+----------+ | 500007984 | 9606 | ENSG00000252303 | snRNA | | 500007981 | 9606 | ENSG00000281771 | misc_RNA | | 500007955 | 9606 | ENSG00000281256 | lincRNA | | 500007998 | 9606 | ENSG00000283272 | sRNA | | 500007951 | 9606 | ENSG00000280864 | lincRNA | +----------------+----------+-----------------+----------+ 5 rows in set (0.85 sec) EXAMPLE: MULTI DATABASE QUERY: ENSG00000131143 (human COX4i1) SELECT GM1.gene_member_id, GM1.stable_id, homology.homology_id, GM2.gene_member_id, GM2.stable_id FROM ensembl_compara_86.gene_member AS GM1 INNER JOIN ensembl_compara_86.homology_member AS HM1 ON (GM1.gene_member_id=HM1.gene_member_id) INNER JOIN homology ON (HM1.homology_id=ensembl_compara_86.homology.homology_id) INNER JOIN ensembl_compara_86.homology_member AS HM2 ON (ensembl_compara_86.homology.homology_id=HM2.homology_id) INNER JOIN ensembl_compara_86.gene_member AS GM2 ON (HM2.gene_member_id=GM2.gene_member_id) WHERE GM1.stable_id='ENSG00000131143' AND GM2.stable_id!=GM1.stable_id; ------------ RESULTS OF A MULTI DATABASE QUERY -------------------------------------------- mysql> use ensembl_compara_86; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ----------+-----------------+-------------+----------------+-----------------------+ | gene_member_id | stable_id | homology_id | gene_member_id | stable_id | +----------------+-----------------+-------------+----------------+-----------------------+ | 1609504 | ENSG00000131143 | 23123697 | 408849 | ENSPTRG00000008434 | | 1609504 | ENSG00000131143 | 23123959 | 366654 | ENSGGOG00000010047 | | 1609504 | ENSG00000131143 | 23124358 | 876169 | ENSPPYG00000007619 | | 1609504 | ENSG00000131143 | 23124883 | 784450 | ENSNLEG00000011409 | | 1609504 | ENSG00000131143 | 23126093 | 1112899 | ENSCSAG00000003144 | | 1609504 | ENSG00000131143 | 23126767 | 1549184 | ENSMMUG00000006763 | | 1609504 | ENSG00000131143 | 23127449 | 788144 | ENSPANG00000010330 | | 1609504 | ENSG00000131143 | 23128122 | 1553505 | ENSMMUG00000041358 | | 1609504 | ENSG00000131143 | 23129771 | 533948 | ENSCJAG00000017319 | | 1609504 | ENSG00000131143 | 23131553 | 965631 | ENSOGAG00000010376 | | 1609504 | ENSG00000131143 | 23134160 | 1147630 | ENSTSYG00000009682 | | 1609504 | ENSG00000131143 | 23134295 | 1533287 | ENSMICG00000010800 | | 1609504 | ENSG00000131143 | 23142569 | 761286 | ENSTBEG00000009494 | | 1609504 | ENSG00000131143 | 23149019 | 1192003 | ENSDORG00000015962 | | 1609504 | ENSG00000131143 | 23151459 | 45650 | ENSOCUG00000001735 | | 1609504 | ENSG00000131143 | 23153907 | 686733 | ENSOPRG00000002129 | | 1609504 | ENSG00000131143 | 23156375 | 321096 | ENSSTOG00000024338 | | 1609504 | ENSG00000131143 | 23158885 | 1364862 | MGP_SPRETEiJ_G0032759 | | 1609504 | ENSG00000131143 | 23161334 | 672098 | ENSCPOG00000022243 | | 1609504 | ENSG00000131143 | 23163826 | 1302840 | ENSRNOG00000017817 | | 1609504 | ENSG00000131143 | 23166315 | 1610009 | ENSMUSG00000031818 | | 1609504 | ENSG00000131143 | 23182331 | 279147 | ENSFCAG00000007612 | | 1609504 | ENSG00000131143 | 23185902 | 1064662 | ENSPVAG00000003757 | | 1609504 | ENSG00000131143 | 23189444 | 1058733 | ENSTTRG00000016023 | | 1609504 | ENSG00000131143 | 23193028 | 937743 | ENSOARG00000011503 | | 1609504 | ENSG00000131143 | 23196626 | 532759 | ENSCAFG00000019921 | | 1609504 | ENSG00000131143 | 23200180 | 236073 | ENSBTAG00000016079 | | 1609504 | ENSG00000131143 | 23203654 | 779954 | ENSMLUG00000012579 | | 1609504 | ENSG00000131143 | 23207088 | 37008 | ENSEEUG00000011840 | | 1609504 | ENSG00000131143 | 23210489 | 919235 | ENSSSCG00000029034 | | 1609504 | ENSG00000131143 | 23213781 | 396039 | ENSECAG00000011722 | | 1609504 | ENSG00000131143 | 23217101 | 229604 | ENSAMEG00000000426 | | 1609504 | ENSG00000131143 | 23220444 | 43501 | ENSAMEG00000018274 | | 1609504 | ENSG00000131143 | 23223744 | 279157 | ENSFCAG00000030952 | | 1609504 | ENSG00000131143 | 23226935 | 1198564 | ENSVPAG00000008506 | | 1609504 | ENSG00000131143 | 23236021 | 1115270 | ENSDNOG00000045013 | | 1609504 | ENSG00000131143 | 23236154 | 1203938 | ENSPCAG00000014776 | | 1609504 | ENSG00000131143 | 23236289 | 255944 | ENSLAFG00000014002 | | 1609504 | ENSG00000131143 | 23249883 | 803304 | ENSSHAG00000005497 | | 1609504 | ENSG00000131143 | 23250023 | 95233 | ENSMODG00000004591 | | 1609504 | ENSG00000131143 | 23258925 | 1162873 | ENSOANG00000003744 | | 1609504 | ENSG00000131143 | 23267840 | 476328 | ENSAPLG00000010205 | | 1609504 | ENSG00000131143 | 23274007 | 1502456 | ENSACAG00000005479 | | 1609504 | ENSG00000131143 | 23280498 | 424225 | ENSFALG00000006638 | | 1609504 | ENSG00000131143 | 23286636 | 1013675 | ENSPSIG00000012864 | | 1609504 | ENSG00000131143 | 23292665 | 1541530 | ENSGALG00000005749 | | 1609504 | ENSG00000131143 | 23298867 | 65542 | ENSMGAG00000007456 | | 1609504 | ENSG00000131143 | 23305113 | 230159 | ENSTGUG00000004109 | | 1609504 | ENSG00000131143 | 23314417 | 141959 | ENSXETG00000004473 | | 1609504 | ENSG00000131143 | 23321753 | 513917 | ENSLACG00000002654 | | 1609504 | ENSG00000131143 | 23337039 | 561275 | ENSGACG00000015963 | | 1609504 | ENSG00000131143 | 23344654 | 1061256 | ENSAMXG00000003399 | | 1609504 | ENSG00000131143 | 23352161 | 16685 | ENSTRUG00000018241 | | 1609504 | ENSG00000131143 | 23359717 | 652184 | ENSTNIG00000000609 | | 1609504 | ENSG00000131143 | 23367103 | 107702 | ENSORLG00000009488 | | 1609504 | ENSG00000131143 | 23374473 | 1216137 | ENSGMOG00000003997 | | 1609504 | ENSG00000131143 | 23381809 | 310116 | ENSLOCG00000000961 | | 1609504 | ENSG00000131143 | 23389146 | 1572832 | ENSDARG00000032970 | | 1609504 | ENSG00000131143 | 23396610 | 893139 | ENSONIG00000005620 | | 1609504 | ENSG00000131143 | 23404037 | 154528 | ENSXMAG00000003100 | | 1609504 | ENSG00000131143 | 23411455 | 415482 | ENSPFOG00000017618 | | 1609504 | ENSG00000131143 | 23419461 | 1486837 | ENSG00000131055 | | 1609504 | ENSG00000131143 | 23432531 | 443916 | ENSCING00000022374 | | 1609504 | ENSG00000131143 | 23449573 | 508328 | FBgn0032833 | | 1609504 | ENSG00000131143 | 23465891 | 1265946 | WBGene00012354 | | 1609504 | ENSG00000131143 | 23482539 | 515739 | FBgn0033020 | +----------------+-----------------+-------------+----------------+-----------------------+ 66 rows in set (1.02 sec) mysql> mysql> SELECT GM1.gene_member_id, GM1.stable_id, homology.homology_id, GM2.gene_member_id, GM2.stable_id FROM ensembl_compara_86.gene_member AS GM1 INNER JOIN ensembl_compara_86.homology_member AS HM1 ON (GM1.gene_member_id=HM1.gene_member_id) INNER JOIN homology ON (HM1.homology_id=ensembl_compara_86.homology.homology_id) INNER JOIN ensembl_compara_86.homology_member AS HM2 ON (ensembl_compara_86.homology.homology_id=HM2.homology_id) INNER JOIN ensembl_compara_86.gene_member AS GM2 ON (HM2.gene_member_id=GM2.gene_member_id) WHERE GM1.stable_id='ENSG00000131143' AND GM2.stable_id!=GM1.stable_id AND GM2.stable_id LIKE 'FB%'; +----------------+-----------------+-------------+----------------+-------------+ | gene_member_id | stable_id | homology_id | gene_member_id | stable_id | +----------------+-----------------+-------------+----------------+-------------+ | 1609504 | ENSG00000131143 | 23449573 | 508328 | FBgn0032833 | | 1609504 | ENSG00000131143 | 23482539 | 515739 | FBgn0033020 | +----------------+-----------------+-------------+----------------+-------------+ 2 rows in set (2.31 sec) # There is one within_species_paralog: mysql> SELECT GM1.gene_member_id, GM1.stable_id, homology.homology_id, GM2.gene_member_id, GM2.stable_id FROM ensembl_compara_86.gene_member AS GM1 INNER JOIN ensembl_compara_86.homology_member AS HM1 ON (GM1.gene_member_id=HM1.gene_member_id) INNER JOIN homology ON (HM1.homology_id=ensembl_compara_86.homology.homology_id) INNER JOIN ensembl_compara_86.homology_member AS HM2 ON (ensembl_compara_86.homology.homology_id=HM2.homology_id) INNER JOIN ensembl_compara_86.gene_member AS GM2 ON (HM2.gene_member_id=GM2.gene_member_id) WHERE GM1.stable_id='ENSG00000131143' AND GM2.stable_id!=GM1.stable_id AND homology.description='within_species_paralog'; +----------------+-----------------+-------------+----------------+-----------------+ | gene_member_id | stable_id | homology_id | gene_member_id | stable_id | +----------------+-----------------+-------------+----------------+-----------------+ | 1609504 | ENSG00000131143 | 23419461 | 1486837 | ENSG00000131055 | +----------------+-----------------+-------------+----------------+-----------------+ 1 row in set (0.09 sec) # How to select only 1-to-1 orthologs: mysql> SELECT GM1.gene_member_id, GM1.stable_id, homology.homology_id, GM2.gene_member_id, GM2.stable_id FROM ensembl_compara_86.gene_member AS GM1 INNER JOIN ensembl_compara_86.homology_member AS HM1 ON (GM1.gene_member_id=HM1.gene_member_id) INNER JOIN homology ON (HM1.homology_id=ensembl_compara_86.homology.homology_id) INNER JOIN ensembl_compara_86.homology_member AS HM2 ON (ensembl_compara_86.homology.homology_id=HM2.homology_id) INNER JOIN ensembl_compara_86.gene_member AS GM2 ON (HM2.gene_member_id=GM2.gene_member_id) WHERE GM1.stable_id='ENSG00000131143' AND GM2.stable_id!=GM1.stable_id AND homology.description='ortholog_one2one'; mysql> select distinct(description) from homology; +------------------------+ | description | +------------------------+ | ortholog_one2one | | within_species_paralog | | ortholog_one2many | | gene_split | | ortholog_many2many | | other_paralog | | alt_allele | +------------------------+ 7 rows in set (45.59 sec) # How to select only hortologs: mysql> SELECT GM1.gene_member_id, GM1.stable_id, homology.homology_id, GM2.gene_member_id, GM2.stable_id FROM ensembl_compara_86.gene_member AS GM1 INNER JOIN ensembl_compara_86.homology_member AS HM1 ON (GM1.gene_member_id=HM1.gene_member_id) INNER JOIN homology ON (HM1.homology_id=ensembl_compara_86.homology.homology_id) INNER JOIN ensembl_compara_86.homology_member AS HM2 ON (ensembl_compara_86.homology.homology_id=HM2.homology_id) INNER JOIN ensembl_compara_86.gene_member AS GM2 ON (HM2.gene_member_id=GM2.gene_member_id) WHERE GM1.stable_id='ENSG00000131143' AND GM2.stable_id!=GM1.stable_id AND homology.description LIKE 'orthol%'; (65 rows, according to the number of the orthologs exported using the ensembl web browser interface)