MYSQL, very slow order by

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 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

Method 3

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/

Method 4

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

Method 5

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating