Posted about 10 years ago. Visible to the public.

MySQL: Do not use "WHERE id IN (SELECT ....)"

Note: This applies specifically to MySQL. In PostgreSQL for example, this is not an issue.

If you care about performance, never use a query like

UPDATE users SET has_message = 1 WHERE IN (SELECT user_id FROM messages)

MySQL does not optimize this and seems to scan the temporary table, which isn't indexed, for every row in the update statement. This applies to other statements than UPDATE as well.

Instead, either use a JOIN like

UPDATE users INNER JOIN messages ON messages.user_id = SET has_message = 1

or pipe it through ruby with something like

user_ids = ActiveRecord::Base.connection.select_values( # Rails 2 user_ids = Message.all.pluck(:user_id) # Rails 3+ User.where(:id => user_ids).update_all(:has_message => 1)

Be aware that MySQL can take many but not too many arguments. Passing a list of 100'000 arguments to an IN statement won't work.

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Owner of this card:

Tobias Kraze
Last edit:
about 3 years ago
by Henning Koch
sub, queries, sub, select, sub, query
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Tobias Kraze to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more