barracuda317 barracuda317 - 2 months ago 13
MySQL Question

Performance of sub-query in IN clause with large tables in delete query

I am wondering, why queries with IN-Operator are much slower than a simple


Let me demonstrate my oberservations on an example:

Query1: SELECT VIDEO_ID FROM videos (about 8000 rows with 1 column)

Query2: DELETE FROM video_snapshot WHERE video_snapshot.VIDEO_ID IN (Query1)

video_snapshot is a very big table with over 7.000.000 rows but VIDEO_ID is indexed, so querys with VIDEO_ID in a
-clause are fast enough.

How does the
-Operator work? I guessed that this is just a short form for serveral

I am using MariaDB 10.1.16 on XAMPP


For more record IN clause performance is very slow in this case you can use INNER JOIN with delete query

DELETE video_snapshot FROM video_snapshot
INNER JOIN videos ON video_snapshot.VIDEO_ID=videos.VIDEO_ID;

In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).. BUT mainly explicit join is faster when need to compare field with other table field.