0

the following executes fast:

delete from text_blocks
where text_id in (
7684,
7683,
7682,
...);

Note, the above list of values is short e.g. 130 retrieved from a separate query. Table text_blocks has ~ 7,000 rows.

The following executes really slow:

delete from text_blocks
where text_id in (select a_text_id from someTable
where name like "%_SomeSuffix");

The subquery in example 2 is the same as used to get the list of 130 in example 1. Neither text_id or a_text_id are indexed.

Any ideas why it is super slow, and or hanging?

bhartsb
  • 1,316
  • 14
  • 39
  • `like "%_SomeSuffix"` <--- this – zerkms Sep 11 '14 at 00:15
  • 1
    @zerkms: But why wouldn't MySql just cache the result, and use the cached results for the `in` clause in the top-level query? Seems like it's running the subquery for every row returned in the top-level query. – Robert Harvey Sep 11 '14 at 00:20
  • seems like it is running the subquery for every row, but why? The sub query runs fast by itself with the wildcard (i.e. executed separately 1 time). The whole idea is that I don't want to run the sub query manually to create the list (and then have to edit and paste it ugh). The _SomeSuffix is so that I can easily identify the rows I've recently added and want to delete. – bhartsb Sep 11 '14 at 00:29
  • @Robert Harvey: because mysql isn't (wasn't?) good in optimizing nested queries. The decent DBMS (oracle, postgresql, sql server) would optimize the given query the same way as `INNER JOIN` or `EXISTS` one. – zerkms Sep 11 '14 at 00:32

1 Answers1

2

MySQL is notorious for having very poor performance for queries/subqueries like

select ...from ... where ... in (select ....);

Try using EXISTS instead of IN and you should see a dramatic improvement. See

http://dev.mysql.com/doc/refman/5.0/en/subquery-optimization-with-exists.html
http://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why

for more info

mti2935
  • 11,465
  • 3
  • 29
  • 33
  • A temporary table helped considerably, but I'd still say it is too slow so I'll look at what you suggest. [http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table](http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table) – bhartsb Sep 11 '14 at 00:59