Question 273652 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel mysql the explain filtered result drops from 100 to 10 when deleted_at is checked

I don’t have any index applied for the deleted_at column.

The "rows" stays the same and everything else except for the "filtered" column which drops from 100 (without checking deleted_at) to 10 (with deleted_at check)

is this a cause for concern or is this just normal behaviour?

The other columns in my query are indexed.

The deleted_at check is automatically applied by Laravel to filter out soft deleted records.

With deleted_at

enter image description here

Without deleted_at

enter image description here

Tags:

2

Answers


  1. 0

    When you add a condition to check the deleted_at column, and the filtered value falls from 100 to 10, it indicates that the condition is lowering the number of rows returned. This can just be typical behavior, so there is no need to worry. But query performance can still be affected by querying without an index on deleted_at, particularly when there are more data. If you frequently use this condition, do index the deleted_at column.

    Login or Signup to reply.
  2. 0

    The "rows" field of EXPLAIN shows you the rows examined, not the rows that are finally returned by the query.

    When an index assists a search, it reduces the number of examined rows, and the "rows" field will reflect that.

    When a query has additional conditions that are not assisted by an index, the "rows" field will stay the same, but some of the rows examined will be filtered by the query conditions. This produces the correct result, but it has to evaluate these rows one by one against the query expression before they can be filtered.

    It’s considered more optimized if the query doesn’t have to examine rows it won’t need in the result, because the index search just skipped those rows. In that case, the "rows" field will be reduced, and the "filtered" will stay at 100%.

    In other words, a low value in "filtered" is not as good. It means the query had to examine and evaluate rows that did not belong in the result set.

    Is it the end of the world? No, of course not. Queries filter by unindexed expressions all the time. Some expressions can’t be improved with an index.

    But if you’re looking for an extra optimization, because this query is unable to finish quickly enough, or you run it against larger and larger datasets, you should consider a change to your indexes.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search