MySQL: Get the number of "passed" SELECT rows

Question

MySQL

Good evening.

Let's say there is a sign: And there is a simple query: How to get the number (exactly the number, the content is not important) of the lines that the command "passed" before getting the result? Those. something like: But at the same time it is necessary to keep within one request. Sense in sampling on the field of type VARCHAR, at sorting on other type INT.

CREATE TABLE `mytable` (
`id` int(11) NOT NULL auto_increment,
`system_name` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

SELECT * FROM `mytable` WHERE `system_name` = 'element_10' ORDER BY `id`


SELECT @id := `id` FROM `mytable` WHERE `system_name` = 'element_10' LIMIT 1;
SELECT * FROM `mytable` WHERE `id` < @id ORDER BY `id`


Answer 1

The question is somewhat strange. In my opinion, the RDBMS will never tell you how many rows it has processed before getting the result. Moreover, the “scan route” and its order will be different in different conditions.

Answer 2

habrahabr.ru/blogs/mysql/130905/ read starting from "Counting the number of lines read"

Answer 3

Just write the procedure. There you will have a pointer to a specific line in which the muscle is.

Answer 4

If the system_name field is not included in the index, then such a request will be a full scan, i.e. 100% of the records in the table will be traversed. You can count the number of records in a table using the count operator.

Answer 5

> And there is a simple query:
> SELECT * FROM `mytable` WHERE `system_name` = 'element_10' ORDER BY `id`
> How to get the number (namely the number, the content is not important) of the rows that the command "traversed" before getting the result ?
Your question is incorrect. Do you think MySQL always rewrites all rows in a row? If there was an index on the system_name field, MySQL would select that row the first time without going through the others.
You probably wanted to ask how to calculate how many rows exist whose id is less than the one found? This can be done with the query
SELECT COUNT(*) FROM mytable WHERE id < {id of the found row}
This query is inefficient, especially on large tables, therefore, if it is needed (for example, it is necessary to calculate where a player with a given number of points is), they make an additional position field, and the cron script recalculates the places every N minutes. The recalculation process can be somehow optimized by storing information about insertions and deletions in a table.