I am running a procedure that inserts 10,000 thousand records into a table. It's taking ages ( over 30 mins ).

The indexes on the table are the primary id and two other columns that are foreign keys. The foreign keys point to id's in other tables which have their primary id indexed.

When I look at sys.schema_index_statistics for the table that is getting the inserts I see a huge number of rows_selected on one of the foreign key indexes ( the value is 3,785,992,740 ). I assume rows_selected is a query. If this is just simple referential integrity checks why is this doing so many queries given that I have only 1000 items in one of the referenced tables and 21000 in the other and both those two tables have indexes on their id then why am I seeing so many rows_selected ?

I am running a procedure that inserts 10,000 thousand records into a table. It's taking ages ( over 30 mins ).

The indexes on the table are the primary id and two other columns that are foreign keys. The foreign keys point to id's in other tables which have their primary id indexed.

When I look at sys.schema_index_statistics for the table that is getting the inserts I see a huge number of rows_selected on one of the foreign key indexes ( the value is 3,785,992,740 ). I assume rows_selected is a query. If this is just simple referential integrity checks why is this doing so many queries given that I have only 1000 items in one of the referenced tables and 21000 in the other and both those two tables have indexes on their id then why am I seeing so many rows_selected ?

• mysql
• performance
• insert

• We can help optimize your stored procedure, if you show it. There's good no reason it should take 30 minutes to insert 10000 records. That would be a new question though. – ysth Commented yesterday

1 Answer 1

As per mysql manual on sys.schema_index_statistics table:

rows_selected: The total number of rows read using the index

This is not per query, this is historical total.