CODEWITHSUNDEEP
mysqlsubquery
Dave Mast
Dave Mast

Reputation: 25

MySQL Subquery: multiple queries on same table optimization

I have a database table that I would like to search twice, the first time to establish a list of entries meeting a certain criteria and then use that list to limit my second one showing ALL entries having the matching 'name'

The following query does what I want, but it takes forever is there an easy alternative that runs more optimally?

SELECT * FROM voting WHERE name IN (SELECT name FROM voting WHERE yob=15)

I also tried, 

SELECT * FROM voting WHERE name = (SELECT name FROM voting WHERE yob=15)

this didn't work at all, but I think shows the logic of what I'm wanting to do. Thanks.

Upvotes: 0

Views: 443

Answers (2)

Adarsh
Adarsh

Reputation: 726

You can try this also, but should have index.

SELECT a.* FROM voting a JOIN
(SELECT DISTINCT NAME FROM voting WHERE yob=15) AS b
WHERE a.name = b.name;

If you have millions of data in your table then this also may stuck for sometime.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can switch to using exists:

SELECT v.*
FROM voting v
WHERE EXISTS (SELECT 1 FROM voting v2 WHERE v2.name = v.name AND yob = 15);

For this query, you want an index on voting(name, yob).

Upvotes: 1

Related Questions