MySQL: Why is simple query not using index, performing filesort

Question

I have a table defined like so:

`id` int(10) NOT NULL AUTO_INCREMENT,
`slug` varchar(150) NOT NULL,
`title` varchar(150) NOT NULL,
`description` text NOT NULL,
`ordinal` int(10) NOT NULL DEFAULT '0'

Let's call it t1

In t1, I have an index on ordinal.

This table only contains a few rows, it's a definitions table so I usually do this, to get the definitions in the order I want them

SELECT * FROM t1 WHERE 1 ORDER BY ordinal;

If I perform an EXPLAIN on that statement, I get the following:

id? select_type?    table?  partitions? type?   possible_keys?  key?    key_len?    ref?    rows?   Extra?
1   SIMPLE  t1  NULL    ALL NULL    NULL    NULL    NULL    5   Using where; Using filesort

It doesn't really matter that the row above is screwed up in alignment. The important part is that it's using filesort and I can't figure out why.

Since it's only 5-10 rows in this table, it can feel like it's not important but the filesort makes my open_tables go a bit bananas since MySQL (according to the mighty internet) opens TWO tables for each filesort query it needs to perform.

So, greatful for any help here. Thanks.

Answer 1

Your table does not have any index on the "ordinal" column that would possibly get utilized. Also, since your WHERE clause is on a fixed "1" value which is always true, but no column to compare or even LOOK for an index to help, it can't pick anything... So you are going through a filesort... no index applicable to the where and no index applicable to the order by clause.