Mysql optimization

Listing every record from table

[zoltan]

Hello,

I have the following sql query:

select recordid from records order by bid desc, fromtime, totalpayments desc, lastlogindate desc, logincounter desc, recordid;
65131 rows in set (1.67 sec)

This query lists every record from table records and sort them. Why does this query take 1.67 seconds?
recordid is primary key
bid, fromtime, totalpayments, lastlogindate and logincounter are indexes

I checked with explain but it does not tell me anything.

explain select recordid from records order by bid desc, fromtime, totalpayments desc, lastlogindate desc, logincounter desc, recordid;
+-----------+------+---------------+------+---------+------+-------+----------------+
¦ table ¦ type ¦ possible_keys ¦ key ¦ key_len ¦ ref ¦ rows ¦ Extra ¦
+-----------+------+---------------+------+---------+------+-------+----------------+
¦ records ¦ ALL ¦ NULL ¦ NULL ¦ NULL ¦ NULL ¦ 65131 ¦ Using filesort ¦
+-----------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

When I go deeper, everything is getting back to normal. The below query is quite fast:
select recordid from records where anothervarchar like 'A%' order by bid desc, fromtime, totalpayments desc, lastlogindate desc, logincounter desc, recordid;
5938 rows in set (0.15 sec)

Why a simple list of every record in a table takes a lot more time? Because of sorting? Is there anything I can do to make this faster?

[txbakers]

Even though you are choosing all records, you are sorting my lots of keys, some ascending, some descending. It takes time for the optimizer to do the acrobatics.

You don't have a straight read there.