MySQL Subquery: multiple queries on same table optimization

Question

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.

Why the need to search it twice? Why would SELECT * from voting WHERE yob = 15 not suffice? — JRD
– JRD, Commented Oct 26, 2015 at 16:47
Good question. The idea is that I have multiple rows with the same name and each of those rows has a different yob, some yobs are 15 some are not. I want to ultimately show ALL ROWS, regardless of YOB, but only for those names that have at least one yob of 15. I hope that makes sense. — Dave Mast
– Dave Mast, Commented Oct 26, 2015 at 17:00

Gordon Linoff · Accepted Answer · 2015-10-26 16:38:31Z

1

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).

answered Oct 26, 2015 at 16:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dave Mast Over a year ago

Indexing was indeed the solution! Thank you so much for your help.

Adarsh · Accepted Answer · 2015-10-26 17:17:19Z

0

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.

answered Oct 26, 2015 at 17:17

Adarsh

7244 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySQL Subquery: multiple queries on same table optimization

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related