Rob, to get better performance from your query, consider splitting the main IN clause out into multiple UNIONed SELECTs. This will perform better than a single one in some cases. Example:
SELECT word_id,tax_id, COUNT(*), num_genes
FROM gene2word
WHERE locuslink_id in ( ... 500 values ... )
GROUP BY word_id, tax_id
UNION ALL
SELECT word_id,tax_id, COUNT(*), num_genes
FROM gene2word
WHERE locuslink_id in ( ... next 500 values ... )
GROUP BY word_id, tax_id
UNION ALL
SELECT word_id,tax_id, COUNT(*), num_genes
FROM gene2word
WHERE locuslink_id in ( ... next 500 values ... )
GROUP BY word_id, tax_id;
This is a variation on the pre 5.0 index merge tricks...
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com