Mysql query with join, limit and order is very slow (filesort)

I have the following query:

explain select * from users, dls where dls.user_id=users.id and users.status = 'accepted' and users.acc = 0 order by users.user_name desc limit 18416, 16

Which results in the following explain;

+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys          | key         | key_len | ref                             | rows  | Extra                           |
+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | dls   | ALL  | PRIMARY,user_id         | NULL        | NULL    | NULL                            | 19910 | Using temporary; Using filesort | 
|  1 | SIMPLE      | users  | ref 开发者_开发知识库 | PRIMARY,id_user_name | id_user_name | 4       | dls.user_id |     1 | Using where                     | 
+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

This query is really, really slow and I cannot figure out how to fix it. I tried all kinds of indexes from reading articles on how to optimize order by / limit queries, but the result remains the same. Can anyone please help?

Edit: schemas:

CREATE TABLE `users` (

  `id` int(10) unsigned NOT NULL auto_increment,
  `user_name` varchar(100) character set utf8 NOT NULL,
`status` enum('accepted','rejected') character set utf8 NOT NULL,
`acc` varchar(6) character set utf8 NOT NULL,
 PRIMARY KEY  (`id`),
  KEY `user_name` (`user_name`),
 KEY `id_user_name` (`id`,`user_name`)
)

CREATE TABLE `dls` (
 `user_id` int(10) unsigned NOT NULL,
 `category_id` bigint(20) NOT NULL,

`download_url` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`user_id`,`category_id`),
  KEY `user_id` (`user_id`)
)

Output for query by Scrummeister;

+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys          | key    | key_len | ref                          | rows  | Extra                       |
+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | u  | ALL  | PRIMARY,id_user_name | NULL   | NULL    | NULL                         | 10838 | Using where; Using filesort | 
|  1 | SIMPLE      | dls   | ref  | PRIMARY,user_id         | user_id | 4       | u.id |     2 |                             | 
+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+

---

MySql is known to have issues with a LIMIT using a large offset.

The STRAIGHT_JOIN keyword, tells MySql to first scan the users table and then for every user, look up the rows in the dls table.

SELECT STRAIGHT_JOIN *
FROM users u JOIN dls ON dls.user_id = users.id
WHERE u.status = 'accepted' and u.acc = 0
ORDER BY users.user_name desc 
LIMIT 18416, 16

Using STRAIGHT_JOIN is not recommended unless there is a need for it, In this specific case i believe it might work since it can use the user_name index for Sorting.

Other options you have:

• Increase the size of sort_buffer_size
• Increase the size of read_rnd_buffer_size (with caution!)
• Doing the paging on the users table only, regardless of how many dls he has, Only than apply the JOIN.
• Handle the paging in your code. Assuming a user goes from page to page with skipping to many, you should store the first & last user names for each page. If the user clicks the next page - Add a WHERE user_name > "{LastPageLastUsername} LIMIT 0,16" this will increase

For other optimization, read ORDER BY Optimization and Limit Optimization

---

Try add an index to the users table with the following columns

status, acc, user_name

or

acc, status, user_name

which ever is the faster