I guess the data only changes when a new genome is added, so this might indeed be the only viable solution. I hadn't thought about it yet. Michiel PS: if anybody else has a brilliant solution, please feel free to submit it :) Thomas Abeel wrote:
How often does the data changes?
If this is not too often, you may want to put these counts in a separate table and update them when you update the other tables. This will probably be the only fast solution.
regards, Thomas
Michiel Van Bel wrote:
Hi,
perhaps you guys can help me with this. we have 2 tables:
* gf_data: 2 columns (gene_family id`s and gene_id`s) . This table has about 500k rows * annotation: lots of columns, but we only need the ones with the gene_id`s and the species. This table has about 300k rows.
We now need a couple of things:
* For each species, we need a count of genes that are in gene_families with a size larger than 1 * For each species, we need a count of gene_families.
Now, I've tried already a lot, but run into the problem of having to use either joins or nested queries, which do not really scale well apparently :s
I thought about creating a view (by appending the gene_family into the annotation table), but it's been such a long time i've done this that I cannot remember whether this will be any help at all.
So, any suggestions?
Michiel
PS: I'll think about a price for the winner :)
------------------------------------------------------------------------
_______________________________________________ Binari mailing list Binari@psb.ugent.be https://maillist.psb.ugent.be/mailman/listinfo/binari
-- ================================================================== Michiel Van Bel PhD student Tel:+32 (0)9 331 36 95 fax:+32 (0)9 3313809 VIB Department of Plant Systems Biology, Ghent University Technologiepark 927, 9052 Gent, BELGIUM mibel@psb.ugent.be http://www.psb.ugent.be ==================================================================