2

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

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 WHERE-clause are fast enough.

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

I am using MariaDB 10.1.16 on XAMPP

Haresh Vidja
  • 8,340
  • 3
  • 25
  • 42
barracuda317
  • 608
  • 7
  • 24
  • Maybe this one can help you: http://stackoverflow.com/a/14194444/1050927 – Prisoner Sep 22 '16 at 09:22
  • 2
    what does explain say – TheGameiswar Sep 22 '16 at 09:27
  • It is slow because you are doing a correlated subquery. Note you never gave a schema for analysis. That would be done with `show create table xyz` for any relevant table. You also did not give `Query1`. So that wasn't very helpful – Drew Sep 22 '16 at 10:18

1 Answers1

3

For large data-set table 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.

Haresh Vidja
  • 8,340
  • 3
  • 25
  • 42