How to remove the glitch of calculating the ordinal number in a MYSQL table?

Question

MySQL

Hello. Faced a mega nasty glitch. The task is to find and calculate the sequence number of a record with a certain ID in a table with more than 500,000 records. I make a request:

SELECT rownum, id FROM (
SELECT @rownum := @rownum +1 AS rownum, companies.id
FROM companies, (SELECT @rownum :=0) r
WHERE `companies`.`delete_date` IS NULL
ORDER BY companies.mng_status DESC
LIMIT 5000
) AS src
WHERE src.id =116912

The result is correct. rownum returns 27.
But if I remove LIMIT 5000 (as it should be) or set it to more than 14800, then the result is generally strange - rownum = 1472 !
EXPLAIN also shows different execution results:
With LIMIT 5000:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ALL NULL NULL NULL NULL 5000 Using where
2 DERIVED system NULL NULL NULL NULL 1
2 DERIVED companies index NULL mng_status 4 NULL 5000 Using where
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
With or without LIMIT 15000:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ALL NULL NULL NULL NULL 15000 Using where
2 DERIVED system NULL NULL NULL NULL 1 Using filesort
2 DERIVED companies ALL NULL NULL NULL NULL 116937
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
Please help. Maybe something with the settings. But I don’t know what and where to increase ...
Thank you!

Answer 1

Good afternoon,
what is the value in the companies.mng_status field that is sorted by?
Let's assume that the top 5000 always returns the first subtracted data for the index with the value mng_status = 1.
in the case when we request all the data or 15000, the skul decides to take not from the index, but to scan the table.
then with the status mng_status = 1 there will be much more data and the specific id in it will not necessarily be in the same place as it was when subtracting from the index, the sorting in the index itself may be different.
you can try adding a hint to use the index and see what happens: USE INDEX (col_index)