CODEWITHSUNDEEP
mysqlindexinginnodbsql-delete
ling swift
ling swift

Reputation: 1

mysql innodb deletes 10000 rows by primary key, but no index is used

I have one MySQL table using the InnoDB storage engine. it contains about 8000000 data rows, When I used 

delete from t where id in(1,2,3...)

to delete 10000 rows of data,It did not hint the primary key index although id is the primary key.The table t has 6 indexes（Including primary key）.When I change the SQL to 

select * from t where id in(1,2,3...)

It can hit the primary key index. When I reduce the number of parameters to delete 5000 rows of data,Or When I delete non-primary key index but still delete 10000 rows of data,It can also hit the primary key index.

The mysql version is 5.7.27. The id to be deleted is selected according to specific conditions, not continuous. when I try to keep two secondary indexes,Can still use the primary key index.

Why did not hit the index despite specifying the primary key value?

What is the relationship between the number of deleted records and the secondary index?

Upvotes: 0

Views: 322

Answers (1)

Rick James
Rick James

Reputation: 142228

What version? A newer version has changed the cutoff between two ways of dealing with a long IN list.

Older versions optimize UPDATE and DELETE differently than SELECT.

I don't like to tackle more than 1000 rows at a time. Be aware that DELETE is a lot more complex than SELECT. The old values of the rows are saved in case of ROLLBACK or recovery from a crash.

Where did you get the ids? It might be better to leave them where they are, and do a multi-table delete to grab the ids from the source, instead of building the list.

For SELECT the cutoff between using a secondary index and doing a table scan is about 20% of the table. The exact cutoff depends on the statistics and the "cost" computations.

Check out my other tips on big deletes: http://mysql.rjweb.org/doc.php/deletebig

Upvotes: 1

Related Questions