[mysql] MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

Rewrite the query into this

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

I think st2.relevant_field must be in the select, because otherwise the having clause will give an error, but I'm not 100% sure

Never use IN with a subquery; this is notoriously slow.
Only ever use IN with a fixed list of values.

More tips

  1. If you want to make queries faster, don't do a SELECT * only select the fields that you really need.
  2. Make sure you have an index on relevant_field to speed up the equi-join.
  3. Make sure to group by on the primary key.
  4. If you are on InnoDB and you only select indexed fields (and things are not too complex) than MySQL will resolve your query using only the indexes, speeding things way up.

General solution for 90% of your IN (select queries

Use this code

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1) 

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to subquery

What is the difference between LATERAL and a subquery in PostgreSQL? Postgres Error: More than one row returned by a subquery used as an expression How does Subquery in select statement work in oracle Difference between Subquery and Correlated Subquery How to do this in Laravel, subquery where in SQL LEFT JOIN Subquery Alias Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression subquery in FROM must have an alias Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable? How can I insert values into a table, using a subquery with more than one result?

Examples related to where-in

SQL use CASE statement in WHERE IN clause MySQL - SELECT WHERE field IN (subquery) - Extremely slow why? Can I bind an array to an IN() condition?