MYSQL, very slow order by

Question

I have got two tables. One is a User table with a primary key on the userid and the other table references the user table with a foreign key.

The User table has only one entry (for now) and the other table has one million entrys.

The following join drives me mad:

 SELECT p0_.*, p1_.*
 FROM photo p0_, User p1_
 WHERE p0_.user_id = p1_.user_id
 ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000

The query takes 12sec on a very fast machine with the order by and 0.0005 sec without the order by.

I've got an index on user_id (IDX_14B78418A76ED395) and a composite index ("search2") on user_id and uploaddate.

EXPLAIN shows the following:

+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys                | key                  | key_len | ref                 | rows  | Extra                           |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
|  1 | SIMPLE      | p1_   | ALL  | PRIMARY                      | NULL                 | NULL    | NULL                |     1 | Using temporary; Using filesort |
|  1 | SIMPLE      | p0_   | ref  | IDX_14B78418A76ED395,search2 | IDX_14B78418A76ED395 | 4       | odsfoto.p1_.user_id | 58520 |                                 |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+

Table definitions:

CREATE TABLE `photo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`album_id` int(11) DEFAULT NULL,
`exif_id` int(11) DEFAULT NULL,
`title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`width` int(11) NOT NULL,
`height` int(11) NOT NULL,
`uploaddate` datetime NOT NULL,
`filesize` int(11) DEFAULT NULL,
`path` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`originalFilename` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`mimeType` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`description` longtext COLLATE utf8_unicode_ci,
`gpsData_id` int(11) DEFAULT NULL,
`views` int(11) DEFAULT NULL,
`likes` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_14B78418B0FC9251` (`exif_id`),
UNIQUE KEY `UNIQ_14B7841867E96507` (`gpsData_id`),
KEY `IDX_14B78418A76ED395` (`user_id`),
KEY `IDX_14B784181137ABCF` (`album_id`),
KEY `search_idx` (`uploaddate`),
KEY `search2` (`user_id`,`uploaddate`),
KEY `search3` (`uploaddate`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `user` (
`user_id` int(11) NOT NULL,
`photoCount` int(11) NOT NULL,
`photoViews` int(11) NOT NULL,
`photoComments` int(11) NOT NULL,
`photoLikes` int(11) NOT NULL,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What can I do to speed up this query?

Can you provide your table definitions for `p0_` and `p1_` and values for `sort_buffer_size` and `read_rnd_buffer_size` — Dancrumb, Jan 16 '13 at 21:43
In short, include `SHOW CREATE TABLE p1_` and `SHOW CREATE TABLE p0_` so we can see how the tables would be created. — Dancrumb, Jan 16 '13 at 21:45
I added table definitions and cannot find the two variables in the my.cnf — user1985207, Jan 16 '13 at 21:52
As an aside, you should really be using `FROM photo p0_ INNER JOIN User p1_ ON p0_.user_id = p1_.user_id` rather than `FROM photo p0_, User p1_ WHERE p0_.user_id = p1_.user_id`, but I don't think this is the cause of your performance issue. — Dancrumb, Jan 16 '13 at 21:55
PhpMyAdmin shows me that nearly the whole time is consumed in "Copying to tmp table" — user1985207, Jan 16 '13 at 21:55
Yes, but the usage of an explicit join makes no difference in execution time in this case — user1985207, Jan 16 '13 at 21:57
@user1985207: should there be a `FOREIGN KEY` between photos and users? Is `photo.user_id` guaranteed to have a match in `user.user.id`? — Quassnoi, Jan 16 '13 at 21:57

Quassnoi · Accepted Answer · 2013-01-16T22:10:46.083

51

Seems you're suffering from MySQL's inability to do late row lookups:

Try this:

SELECT  p.*, u.*
FROM    (
        SELECT  id
        FROM    photo
        ORDER BY
                uploaddate DESC, id DESC
        LIMIT   10
        OFFSET  100000
        ) pi
JOIN    photo p
ON      p.id = pi.id
JOIN    user u
ON      u.user_id = p.user_id

edited Jan 16 '13 at 22:10

answered Jan 16 '13 at 21:58

Quassnoi

413,100
91
616
614

Thank you, that is much faster and I see the point. But why is the query optimizer not doing this simple optimization? – user1985207 Jan 16 '13 at 22:04
@user1985207: because it's not programmed to do so. See the links. – Quassnoi Jan 16 '13 at 22:11

score 2 · Answer 2 · answered Jan 16 '13 at 21:34

2

You need a separate index on uploaddate. This sort will take advantage of composite index only if uploaddate is first column in it. You can also try to add user_id to ORDER BY:

    ....      
    ORDER BY p0_.user_id, p0_.uploaddate

answered Jan 16 '13 at 21:34

Cozzamara

1,318
1
14
22

there is also a composite index on uploaddate, user_id but it isn't used – user1985207 Jan 16 '13 at 21:35
Adding user_id to the ORDER BY doesn't make any difference – user1985207 Jan 16 '13 at 21:41
what if you swap user_id and uploaddate in ORDER BY ? – Cozzamara Jan 16 '13 at 21:45
It makes no difference in execution time – user1985207 Jan 16 '13 at 21:53

score 2 · Answer 3 · edited Jul 04 '20 at 15:40

2

You have two problems:

You need to create an INDEX(user_id, uploaddate) which will greatly increase the efficiency of the query.
You need to find a workaround to using LIMIT 10 OFFSET 100000. MySQL is creating a recordset with 100,000 records in it, then it pulls the last 10 records off the end... that is extremely inefficient.

https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/

edited Jul 04 '20 at 15:40

Happy

757
9
18

answered Jan 16 '13 at 21:45

Ian

24,116
22
58
96

It's the same if I only use limit and remove the whole offset or set it to zero – user1985207 Jan 16 '13 at 21:50
`LIMIT 10` and `LIMIT 10, 100000` are very different in terms of performance. The primary reason your query is slow however, is the lack of indexes being used. When you run `EXPLAIN` on your query, it should be using indexes and never do a `ALL`. – Ian Jan 16 '13 at 21:58
I know, but what index is missing. I think the "ALL" is used because there is only one entry in user. – user1985207 Jan 16 '13 at 22:00
1

The link URL has been changed as: https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/ – Happy Jul 04 '20 at 04:06

score 0 · Answer 4 · answered Jan 27 '17 at 18:21

First try to get result based on primary key with out join and use result to query result again.
For ex:

$userIds=mysql::select("select user_id from photo ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000");

$photoData=mysql::select("SELECT p0_., p1_. FROM photo p0_, User p1_ WHERE p0_.user_id = p1_.user_id and p0_.user_id in ($userIds->user_id) order by p0_.uploaddate");

Here we had divided the statement into two parts:
1.We can easily order and get based on primary key and also there are no joins.
2.Getting query results based on id and order by is only on limited columns we can retrieve data in less time

Dorin · Answer 5 · 2018-07-26T09:13:50.660

From 30 seconds to 0.015 sec / 0.000 sec using Quassnoi answer ! This is what I called MySql expertise ! I cut out one Join from my personal project (no join with itself)

Select ser.id_table, ser.id_rec, ser.relevance, cnt, title, description, sell_url, medium_thumb,  
        unique_id_supplier, keywords width, height, media_type 
from (    
        Select ser.id_rec, ser.id_table, ser.relevance, ser.cnt 
        from searchEngineResults ser     
        where thisSearch = 16287     
    order by  ser.relevance desc, cnt desc, id_rec 
    ) ser 
join photo_resell sou on sou.id = ser.id_rec 
#join searchEngineResults ser on ser.id_rec = tmp.id_rec 
limit 0, 9

MYSQL, very slow order by

5 Answers5

Linked