3

I have a table with 4.7 million rows and I am executing this query on this table:

SELECT * FROM students WHERE `eid` IN 
(SELECT DISTINCT `eid` FROM students WHERE code IN ('120', '330'));

This is working fine but it slows down the performance. How can I boost up performance by changing this query or how can I do the same work with some other optimized query (in better way)?

3
  • 2
    Which version of MySQL? It would be good to add the SHOW CREATE students; output and the EXPLAIN output in the question as well. – ypercubeᵀᴹ Sep 21 '16 at 11:21
  • 2
    The distinct in the sub-query is not needed and I doubt MySQL will optimize that way – a_horse_with_no_name Sep 21 '16 at 11:43
  • 3
    IN can be really slow for large sets. Try using a proper join. – Andrew Brennan Sep 21 '16 at 12:30
0

Having no idea of your table... The first thing to try would be to

CREATE INDEX i_code ON students (code);
CREATE INDEX i_eid ON students (eid);
0

Just use an EXISTS clause.. Generally faster and safer.

SELECT *
FROM students AS s1
WHERE EXISTS (
  SELECT `eid`
  FROM students AS s2
  WHERE code IN ('120', '330')
  AND s1.eid = s2.eid
);

Or, if it's really that simple, and you're just making assumptions about the planner in an attempt to make it faster, benchmark it against this. I'm not sure exactly why you're asking about this, but IN is usually not what you want.

SELECT *
FROM students AS s1
WHERE code IN ('120', '330');
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.