Understanding sys.schema_index_statistics

Question

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 ?

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
– ysth, Commented Feb 9 at 19:28

Shadow · Accepted Answer · 2025-02-09 07:36:36Z

0

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.

answered Feb 9 at 7:36

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

P.Salmon Feb 9 at 7:55

Read since when?

Shadow Feb 9 at 12:44

@P.Salmon since your performance schema is operational from

Scrimpy · Accepted Answer · 2025-02-11 09:16:40Z

0

I found my problem. I was using SET @random_id = (SELECT id FROM logs ORDER BY RAND() LIMIT 1);

The ORDER BY RAND() caused the a full table scan to occur.

answered Feb 11 at 9:16

Scrimpy

351 silver badge6 bronze badges

Collectives™ on Stack Overflow

Understanding sys.schema_index_statistics

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related