How to optimize order by and limit?

I have a client who wishes for me to make a back-end for his website. He requires a table displaying all files with pagination.

CREATE TABLE `content_files` (
    `id` varchar(16) NOT NULL,
    `owner` varchar(16) DEFAULT NULL,
    `location` varchar(16) NOT NULL,
    `parent` varchar(16) DEFAULT NULL,
    `date` int(11) NOT NULL,
    `filename` varchar(256) NOT NULL,
    `username` varchar(64) NOT NULL,
    `email` varchar(256) NOT NULL,
    `ip` varchar(15) NOT NULL,
    `json` text NOT NULL,
    `bin` blob NOT NULL
);

ALTER TABLE `content_files`
     ADD PRIMARY KEY (`id`),
     ADD UNIQUE KEY `ID` (`id`),
     ADD KEY `id_2` (`id`),
     ADD KEY `date` (`date`),
     ADD KEY `filename` (`filename`(255)),
     ADD KEY `username` (`username`(63)),
     ADD KEY `email` (`email`(255)),
     ADD KEY `ip` (`ip`(14));

The items which need to be sortable are date, filename, username, email, and ip. There are currently 65,000 records. If the limit is high, as expected it takes longer, but it's very significantly longer. 100 seconds to get the 60,000th entry.

I was simply using:

SELECT id, date, filename, username, email ip
FROM content_files
ORDER BY filename
LIMIT 60000, 20

I have searched this issue, however, none of the tips seemed to improve my queries. Is there some glaring mistake I am missing in his schema? How can I optimize this?

Solution

You're building up a big dataset and sorting it, only to discard 60K rows and show 20. That work can be cut down by a so-called deferred join. The sorting still has to happen, but it can take less memory, and so be faster.

Edit get the subquery into a join.

 SELECT a.id, a.date, a.filename, a.username, a.email ip
   FROM content_files a 
   JOIN (   SELECT id
              FROM content_files
          ORDER BY filename
             LIMIT 60000, 20
        ) b ON a.id = b.id
  ORDER BY a.filename

This does your great big sort - discard operation on a smaller dataset. It then looks up all the data you need for just the 20 rows.

Finally, if you add a compound index on (filename, id) the subquery can be satisfied by scanning the index which will make it even faster. You can delete the index you have on just filename when you create the compound index.

You have a bunch of redundant indexes on your table. (Three alone on id). Clean up your indexes! They slow down updates.