6

My Query:

EXPLAIN EXTENDED SELECT  `artwork`.`id` ,  `artwork`.`added` 
FROM  `artwork` 
ORDER BY  `artwork`.`added` DESC 
LIMIT 0 , 6

When I added an index on "added" to avoid using filesort and use index instead the output of explained went from

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE artwork ALL NULL NULL NULL NULL 302 100.00 Using filesort

to

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE artwork index NULL added 4 NULL 6 5033.33

and I'm concerned about the filtered going up approximently 4,100 - I can't find on Google what filtered means

Riedsio
  • 9,758
  • 1
  • 24
  • 33
Ben
  • 60,438
  • 111
  • 314
  • 488

2 Answers2

6

Hey, it's actually good news. It shows how many rows your limitations are removing from the result set. In this case, that would be your LIMIT statement. See the manual:

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED.

Conspicuous Compiler
  • 6,403
  • 1
  • 40
  • 52
  • Ok, so you're saying it's good, but I still don't quite understand what it means... – Ben Nov 10 '10 at 00:29
  • 1
    @Webnet It means that, without your `LIMIT` clause, `filtered`% __additional__ rows would be returned instead. Try removing your `LIMIT` clause and you'll see the filtered number drop to 0 and the `rows` become the total number of rows in the table. – Conspicuous Compiler Nov 10 '10 at 00:30
3

From the docs:

filtered

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED. (New in MySQL 5.1.12)

Basically, it's ratio of records returned to records fetched.

Its goal is to show how selective your WHERE / ON conditions are and what would be the benefit of creating an index on them.

Note that this field has little sense for the queries with LIMIT, since it's always calculated without regard to the LIMIT clause, which rows is calculated with regard to the latter.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614