Order by performance problems

I have an index problem with my order by statement. I have this query witch is runing fast:

SELECT name from members where kat = 2 order by date DESC;

I have a composite index on members table:

kat_index(kat,date);

Now my aplication has changed and i have added a query like this:

SELECT name from members where kat = 2 order by logged_in DESC, status DESC, date DESC;

Now the query i slow because it's using filesort. So the question is... What开发者_运维技巧 type of index should i add to satisfy both queries?

You should have two indexes:

(kat, date)
(kat, logged_in, status, date)

Each query will use the index that is best for that query.

You cannot cover both queries with one index. You must form a leftmost prefix on the composite index along with the ORDER BY clause as well.