Geek Answers Handbook

MYSQL, very slow order

I have two tables. One of them is a user table with a primary key in the user ID, and another table refers to a user table with a foreign key.

There is only one record in the user table (at the moment), and one million records in the other table.

The following connection drives me crazy:

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 request takes 12 seconds on a very fast machine with order and 0.0005 seconds without order.

I have a user_id index (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 expedite this request?

+6
source share
4 answers

It seems you are suffering from MySQL being unable to search at the end of the line:

Try the following:

 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
+17
source

For uploaddate you need a separate index. This sort will use a composite index only if uploaddate is the first column in it. You can also try adding user_id to ORDER BY:

  .... ORDER BY p0_.user_id, p0_.uploaddate
+1
source

You have two problems:

  • You need to create INDEX(user_id, uploaddate) , which will significantly increase the efficiency of the request.

  • You need to find a workaround to use the LIMIT 10 OFFSET 100000 . MySQL creates a recordset with 100,000 records in it, then it pulls the last 10 records from the end ... this is extremely inefficient.

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ talks about the big problem of constraint / bias.

+1
source

First, try to get the result based on the primary key with disconnecting the connection and use the result to re-query the result.
For instance:

$ 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 p0_.uploaddate");

Here we divided the expression into two parts:
1. We can easily order and receive on the basis of the primary key, and also there are no associations.
2. Getting query results based on id and order by only on limited columns, we can receive data in less time

0
source

More articles:


All Articles