E
E
ev092018-02-27 08:38:56
MySQL
ev09, 2018-02-27 08:38:56

How to limit Rows_examined in MYSQL using LIMIT?

SELECT m.`Message_ID`, m.`Keyword`
FROM `XXX` AS m
WHERE m.`Sub_Class_ID` = 568
AND m.`Keyword` = 'BBB' LIMIT 1;
# Query_time: 5.306829 Lock_time: 0.000655 Rows_sent: 1 Rows_examined: 86744
Why is it examining all rows? The idea is to find the first match and stop.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Alex McArrow, 2018-02-27
@AlexMcArrow

How MySQL executes your query (approximately), on behalf of the DBMS:
What a person wants

SELECT m.`Message_ID`, m.`Keyword` FROM `XXX` AS m WHERE m.`Sub_Class_ID` = 568 AND m.`Keyword` = 'BBB' LIMIT 1;
?
OK, I need to take data from the table `XXX`, namely the fields Message_ID и Keyword, but not just, but with the condition that: Sub_Class_ID = 568 и Keyword = 'BBB'- do it ...
Yeah, got it.. what else? Oh, we need only one line, we take from what we got - ONE line.
Well, more technically, the LIMIT operator is not a data selection condition, but only a subsequent data handler, the same as GROUP and ORDER.
Figuratively - we can say that the DBMS executes two queries: it receives the necessary data set that falls under the condition - it places it in a temporary table and then applies "filters" - GROUP, ORDER, LIMIT.
I could be wrong on the details - but that's the crux of your problem.

M
Melkij, 2018-02-27
@melkij

Rows_examined - How many rows were examined by the query, not how many were found. Before finding a row matching the condition, the query went through a certain number of rows and they did not fit the search condition. What's with the indexes on the plate?
And not to read everything when there is a limit - even mysql can.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question