CODEWITHSUNDEEP
mysql
Anonymous
Anonymous

Reputation: 1373

MySQL query with subquery containing IN is very slow

I am having this problem with my query being slow and I believe it is because of my IN statement in the query.

It looks like this:

SELECT * FROM genes g, 
    (SELECT DISTINCT gene_symbol, score FROM page_genes, genes WHERE 
page_genes.page_id IN
    (SELECT page_id FROM findzebra2.page WHERE umls_concept_id ='c0007361')) AS t
WHERE g.gene_id = t.gene_symbol

I have heard of using a JOIN statement instead but could not figure out how to replace it.

Tables:

genes

genes

page_genes

page_genes

findzebra2.page

findzebra2.page

Expected output

It should be the same table as genes but with filtered data corresponding to the query.

Upvotes: 2

Views: 85

Answers (2)

denny
denny

Reputation: 2254

if i understood your question then your query shold be

SELECT g.* FROM genes g JOIN page_genes p on g.gene_id = p.gene_symbol JOIN findzebra2 f ON p.page_id = f.page_id WHERE f.umls_concept_id = 'c0007361';

Upvotes: 3

Naveed Ramzan
Naveed Ramzan

Reputation: 3593

Try this one. 

select g.*
from genes g
join page p on p.gene_symbol = g.gene_id and p.umls_concept_id = 'c0007361'
join page_genes pg on pg.page_id = g.gene_id

Upvotes: 0

Related Questions