CODEWITHSUNDEEP
mysqlexplain
Pascal
Pascal

Reputation: 55

MySQL Explain - what this mean : Filtered 100.0 but rows is low

My name is Pascal and i have a question on the EXPLAIN function in MYSQL

I have a table Logs_activeusers (id, ifuser, dateLog)

I have an index on dateLog (timestamp)... In this query :

explain SELECT * FROM `logs_activeusers` WHERE datelog>1583703429

The Explain table show me this : rows = 10412 filtered = 100.0

Does that mean that the query pass over all rows ? Because i have 2 millions rows.. So why filtered is 100.0 and rows is only 10412 ?

Does my query is OK ?

Because i don't want the query looks at all rows..

Thanks a lot !

Pascal

Upvotes: 2

Views: 2333

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

rows refers to the number of rows examined, in other words, how many rows need to be read after narrowing the search down using indexes.

filtered is the percentage of examined rows that end up satisfying the conditions of the WHERE clause. You might have a query that applies additional conditions on examined rows, without being aided by an index. Non-indexed conditions are evaluated row by row on each examined row.

Example:

SELECT * FROM `logs_activeusers` WHERE datelog>1583703429 AND user = 123

Now suppose for this query, it uses an index on datelog but that's the only column of that index. Certainly a subset of the examined rows also match the other condition on user. It'll examine 10412 rows, testing each row one at a time agains the condition on user, and discarding those that don't match.

filtered = 100.0 isn't necessary bad — it simply means all the examined rows do meet the conditions of the query.

See also: https://dev.mysql.com/doc/refman/8.0/en/condition-filtering.html

Upvotes: 3

Henru
Henru

Reputation: 133

Check out documentation here: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_filtered

The 'rows' value is the number of rows output after your query. If you have a rows = 10412, after the query is done, you have that many that satisfy your SELECT call.

The 'filter' value represents a percentage of the sample that MySQL looks at and returns. You can take a more in depth look here: https://dba.stackexchange.com/questions/164251/what-is-the-meaning-of-filtered-in-mysql-explain Basically MySQL is giving you an estimate on how efficient your query is based on guesses that the engine is making using indexes. You should not base too much on this value.

Upvotes: 1

Related Questions